Query to replace special characters with _

Question

Post reply

Query to replace special characters with _

Beulah Kingsly

SSCrazy

Points: 2246
More actions
June 3, 2008 at 12:40 pm

#183040

I need to check for the existence of any character other than a–z, A–Z, 0–9, –(hyphen), and _ (underscore) in a string and replace the character by an underscore. What is the best way to do this? The string here will be a field in a table and this will be in the select part of a query in a stored procedure.

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

you can create a UDF to parse a string then use it on the SELECT list.

* Noel

GSquared SSC Guru Points: 260824 More actions · Answer 2

A CLR Regex will be the fastest way to do this.

Check out this thread on the subject:

http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Beulah Kingsly SSCrazy Points: 2246 More actions · Answer 3

Beulah Kingsly

SSCrazy

Points: 2246

June 3, 2008 at 1:42 pm

#823847

Thanks for the help. I could go with regex.

noeld SSC Guru Points: 96590 More actions · Answer 4

definitely a CLR function will be the best bet ...IF ... you have CLR enabled 😉

* Noel

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Lacking an XLATE() type built-in function in T-SQL, CLR is probably the way to go.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

So, the question would be...

... are you allowed to use CLR's on your system?

--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)