Replace Funny Character in a String in SQL Server

Question

I need help to replace funny character in a column in SQL Server,

I have data like this:

id        itemDesc
----------------------------------------------
1         Ball lock pins/ spring typeáááááá
2         Res 1.5k Ohm û R0805 1%

If itemDesc contains á, then replace it with " "; if it contains (û), replace it with -. I used charindex but not change at all especially with many funny characters like id = 1, so if i used charindex, "Ball lock pins/ spring typeáááááá" => "Ball lock pins/ spring type ááááá"

Any approach? thanks for help

Do you only care about those 2 special characters? FYI: You can change the collation to remove all accents; e.g. https://stackoverflow.com/a/32047075/361842 - different to what you've specified in your question, but perhaps gives an alternate solution to your underlying issue? — JohnLBevan, Jun 26 '18 at 08:32

Sebastian Brosch · Accepted Answer · 2018-06-26T08:25:52.840

You can use REPLACE to replace the characters on the string:

SELECT REPLACE(REPLACE(itemDesc, 'á', ' '), 'û', '-') FROM table_name

In case you want to UPDATE the value on column itemDesc you can use the following:

UPDATE table_name SET itemDesc = REPLACE(REPLACE(itemDesc, 'á', ' '), 'û', '-')

The function CHARINDEX can't be used to replace the characters, but to find them. So you can UPDATE or SELECT only the rows with these characters using CHARINDEX:

SELECT REPLACE(REPLACE(itemDesc, 'á', ' '), 'û', '-') 
FROM table_name
WHERE CHARINDEX('á', itemDesc) > 0 OR CHARINDEX('û', itemDesc) > 0

UPDATE table_name SET itemDesc = REPLACE(REPLACE(itemDesc, 'á', ' '), 'û', '-')
WHERE CHARINDEX('á', itemDesc) > 0 OR CHARINDEX('û', itemDesc) > 0

demo: http://www.sqlfiddle.com/#!18/6e241/1/0

score 3 · Answer 2 · answered Jun 26 '18 at 08:22

3

If you're using SQL Server 2017, you can use TRANSLATE:

SELECT TRANSLATE(itemDesc, 'áû',' -') AS itemDescTidy
FROM table_name;

This is a little more succinct than a nested REPLACE (but is actually identical, as it's a "short-hand" function).

answered Jun 26 '18 at 08:22

Thom A

88,727
11
45
75

Replace Funny Character in a String in SQL Server

2 Answers2