How to Remove Special Characters except Space

Question

How to Remove Special Characters except Space

suresh0534

SSCommitted

Points: 1966
More actions
March 9, 2015 at 11:28 am

#145664

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,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

A CLR TVF is the fastest and best, in my opinion. Is that an option for you?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

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.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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]

suresh0534 SSCommitted Points: 1966 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)