Thursday, 5 June 2014

SQL Server 2008 : How to Remove Special Characters in a String, STUFF and PATINDEX Examples

Hi friends, in this article I will explain about  How to Remove Special Characters in a String in SQL Server 2008   
PATINDEX:
It returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
For replacing special characters from string with spaces need to use PATINDEX.
Syntax:
PATINDEX ( '%pattern%' , expression )

Example:
DECLARE @Str varchar(100)
SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+'
SELECT PATINDEX('%SQL%', @Str)

The output of the above code as shown in the below figure.

Remove Special Characters from String in SQL Server 2008
DECLARE @regexp INT
DECLARE @Str varchar(100)
SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+   '
SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
WHILE @regexp > 0
BEGIN
SET @Str = STUFF(@Str, @regexp, 1, ' ' )
SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
Print @regexp
END
SELECT @Str

The output of the above code as shown in the below figure.

Stuff:
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

Example:
 DECLARE @regexp INT
 DECLARE @Str varchar(100)
 SET @Str='welcome to sql server'
 SET @Str = STUFF(@Str, 1, 1, '@' )
 Select @str


The output of the above code as shown in the below figure.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.