How to replace a diacritic (accent character) by a '-'

Question

How to replace a diacritic (accent character) by a '-'

Henk Schreij

SSCarpal Tunnel

Points: 4554
More actions
December 10, 2007 at 2:37 am

#63128

In SELECT Name FROM Mytable I get a few names with a diacritic (eg é, ä) in it.
I use these names for sending them by http, not allowing a diacritic char in it.
Is there a way to replace a diacritic by a minus sign ('-')?
Any suggestion is welcome.
Btw I don't want escape sequences, just a '-' sign.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

- did you check the replace statement ?

- maybe a function replacing them would serve you better (in stead of having to code it every single time over and again)

and it may ease maintenance...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 2

Yes, I did use REPLACE:

[font="Courier New"]SELECT REPLACE(REPLACE(AName, 'ä', '-'), 'é', '-') AS AName[/font]

But then came another diacritic, and then again, and again, etc.

I found out there are ca 35 diacritics in the ascii table: #128 .. #154 and #160 .. #168.

What I want is only @, A..Z, a..z, 0..9, - accepted, and all other chars replaced by a '-'

So ascii 48..57, 64..90, 97..122 and '-' (45) accepted and all other chars replaced by a '-'

I could add a REPLACE for every not accepted char, but there must be a better way 🙂

(I hope)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Also look at this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

N 56°04'39.16"
E 12°55'05.25"

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 4

Sorry, I wasn't looking for a UDF.

What I want is something likea patindex to find the char and then stuff it with a -.

Something like

PATINDEX ('%[^a-z 0-9 . _}%', AName).

I found that only just one occurrence of a diacritic can happen in AName

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Henk Schreij (12/10/2007)
Sorry, I wasn't looking for a UDF.
What I want is something likea patindex to find the char and then stuff it with a -.
Something like
PATINDEX ('%[^a-z 0-9 . _}%', AName).
I found that only just one occurrence of a diacritic can happen in AName

Isn't PATINDEX a function?

What makes generic PATINDEX better than another function designed specifically for your needs?

_____________
Code for TallyGenerator