CODEWITHSUNDEEP
sqlsql-servert-sql
Rudi Encok
Rudi Encok

Reputation: 75

Replace Funny Character in a String in SQL Server

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

Upvotes: 1

Views: 539

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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

Upvotes: 3

Thom A
Thom A

Reputation: 95557

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

Upvotes: 3

Related Questions