Pages

Friday, August 9, 2013

SQL function to Remove all Special Characters except the characters which you wants.


Step 1: Write Script for function

CREATE FUNCTION [dbo].[RemoveSpecialChars] (@string VARCHAR(1000)) 
RETURNS varchar(1000) 
AS 
BEGIN 

DECLARE @validValues varchar(200)='@/,.[](/) ''{}'     -- enter characters which you wants
DECLARE @validTable table(validkeywords varchar(200))
DECLARE @ids INT=1
WHILE @ids<=DATALENGTH(@validValues)
BEGIN
INSERT INTO @validTable VALUES(LTRIM(RTRIM(ASCII(SUBSTRING(@validValues,@ids,1)))))
SET @ids=@ids+1
END

DECLARE @short varchar(100)
DECLARE @index INT =1
DECLARE @str varchar(1000)=''
WHILE @index <= DATALENGTH(@string) 
BEGIN 
       SET @short=ASCII(SUBSTRING(LOWER(@string), @index, 1))
       IF (@short BETWEEN 97 AND 122) OR (@short BETWEEN 48 AND 57)  OR (@short IN (SELECT DISTINCT * FROM @validTable))
       BEGIN 
       SET @str=@str+SUBSTRING(@string, @index, 1)
       END
       SET @index =@index+1
END 
RETURN @str
END

Step 2: Execute function to create it (press F5 to execute)

Step 3: After create the function,  pass string as a parameter to get result

select dbo.RemoveSpecialChars('sds{///}\||+-()*. dsdsSWE.s@3223')


Output will be: sds{///}(). dsdsSWE.s@3223

No comments:

Post a Comment