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.

No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author