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. 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
– JohnLBevan, Commented Jun 26, 2018 at 8:32

Sebastian Brosch · Accepted Answer · 2018-06-26 08:25:52Z

3

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

edited Jun 26, 2018 at 8:25

answered Jun 26, 2018 at 8:19

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2018-06-26 08:22:27Z

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, 2018 at 8:22

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

Replace Funny Character in a String in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related