March 9, 2015 at 11:28 am
HI,
Remove Special Characters except Space.
DECLARE @UNAME VARCHAR(100)
SET @UNAME='KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.:D'
Select @UNAME
Output Shouldbe like KRANTHI KUMAR GOUD
Thanks,
March 9, 2015 at 11:38 am
A CLR TVF is the fastest and best, in my opinion. Is that an option for you?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 9, 2015 at 11:53 am
Phil Parkin (3/9/2015)
A CLR TVF is the fastest and best, in my opinion. Is that an option for you?
Using RegEx might be the quickest way to identify and remove these characters. Not sure if you can do it without CLR.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 9, 2015 at 12:57 pm
Hi,
I got the Solution.
Below is the Solution.
DECLARE @STR VARCHAR(400)
--Add your specialcharacters here
DECLARE @specialchars VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!^?:]%'
SET @STR = 'KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.:D'
WHILE PATINDEX( @specialchars, @STR ) > 0
---Remove special characters using Replace function
SET @STR = Replace(REPLACE( @STR, SUBSTRING( @STR, PATINDEX( @specialchars, @STR ), 1 ),''),'-',' ')
SELECT @STR
March 9, 2015 at 1:27 pm
suresh0534 (3/9/2015)
Hi,I got the Solution.
Below is the Solution.
DECLARE @STR VARCHAR(400)
--Add your specialcharacters here
DECLARE @specialchars VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!^?:]%'
SET @STR = 'KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.:D'
WHILE PATINDEX( @specialchars, @STR ) > 0
---Remove special characters using Replace function
SET @STR = Replace(REPLACE( @STR, SUBSTRING( @STR, PATINDEX( @specialchars, @STR ), 1 ),''),'-',' ')
SELECT @STR
Well done on finding a solution. Calling it 'the solution' is, however, another matter, as there are others.
Should you find that the performance of this solution is a problem, or that you would prefer to go with a 'white list' solution (where you specify the valid characters rather than those which are invalid), please post back.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 10, 2015 at 6:23 pm
suresh0534 (3/9/2015)
Hi,I got the Solution.
Below is the Solution.
DECLARE @STR VARCHAR(400)
--Add your specialcharacters here
DECLARE @specialchars VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!^?:]%'
SET @STR = 'KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.:D'
WHILE PATINDEX( @specialchars, @STR ) > 0
---Remove special characters using Replace function
SET @STR = Replace(REPLACE( @STR, SUBSTRING( @STR, PATINDEX( @specialchars, @STR ), 1 ),''),'-',' ')
SELECT @STR
You don't need commas between every character in @SpecialCharacters. Also, what will you do for special characters that you've left out and the special characters that don't even show up on the usual 101 style keyboard? Last but not least, how are you proposing cleaning a whole column of strings?
I know how to do those things but I'm interested in what your solution would be for those things.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply