Friday, April 17, 2009

How to remove Invalid character in Sql server string.

How to remove Invalid character in Sql server string.

How to remove Invalid character in Sql server string.
Suppose you have a string in sql sever which contains Invalid charasters lik *&^%$ etc.
you want plain string without this characters. I develop one function for that ..

this function takes two arguments
(1)String which contains Invalid characters
(2)string of characters whic need to be removed.
and resurns plain string.


CREATE Function RemoveInvalid
(
@InputString varchar(8000),
@InvalidChars varchar(20)
)returns varchar(8000)
as
BEGIN
declare @strMain varchar(8000)
declare @charsToReplace varchar(20)
set @charsToReplace =@InvalidChars
set @strMain=@InputString
declare @i int
set @i=len(@charsToReplace)
while(@i>0)
Begin
declare @tmp char
set @tmp= substring(@charsToReplace,@i,len(@charsToReplace))
set @strMain= replace(LTRIM(RTRIM(@strMain)),@tmp,'')
set @i=@i-1
End
Return @strMain
END



Suppose You Have string Like "'haresh#*^ xxyy','*x #^"
and you want to remove "*x #^" . this function will remove *,space,#,^ from given string.and returns only "hareshyy"

Thanks.