SQL get all records which contains invalid character for e-diaeresis

Question

I have some values in table which text contains A« instead of ë

How to replace those characters with ë in SQL ?

I tried with

SELECT * 
FROM [dbo].[Table] 
WHERE [dbo].[Table].CustomValue LIKE '%A«%'

How to update those items in query ?

Shouldn't it be `SELECT * FROM [dbo].[Table] WHERE [dbo].[Table].CustomValue LIKE '%A«%'`? — Rain336, Apr 14 '20 at 07:45
@Rain336 Ok, awesome, I forgot `LIKE` :) ... How to update those character with correct one ? — Snake Eyes, Apr 14 '20 at 07:51
Do you want to *select* corrected text or do you want to *update your table* with corrected text? — Thorsten Kettner, Apr 14 '20 at 07:53
Does this answer your question? [PHP & mySQL - ë written as Ã«](https://stackoverflow.com/questions/7609509/php-mysql-%c3%ab-written-as-%c3%83) — jef, Apr 14 '20 at 07:57

score 0 · Answer 1 · answered Apr 14 '20 at 07:57

0

An UPDATE statment with the REPLACE function should do the trick.

UPDATE [dbo].[Table]
SET CustomValue = REPLACE(CustomValue, 'A«', 'ë')
WHERE CustomValue LIKE '%A«%'

answered Apr 14 '20 at 07:57

Rain336

Ilyes · Accepted Answer · 2020-04-14T08:10:49.083

0

If you want just to SELECT the data then

SELECT *, REPLACE(CustomValue, 'A«', N'ë')
FROM [dbo].[Table] 
WHERE [dbo].[Table].CustomValue LIKE '%A«%'

If you really needs to UPDATE the data then

UPDATE [dbo].[Table]
SET CustomValue = REPLACE(CustomValue, 'A«', N'ë')
WHERE [dbo].[Table].CustomValue LIKE '%A«%'

edited Apr 14 '20 at 08:10

answered Apr 14 '20 at 07:58

Ilyes

score 0 · Answer 3 · answered Apr 14 '20 at 08:00

Maybe you are looking just for

update dbo.table 
set customvalue = replace(customvalue, 'A«', 'ë')
where customvalue like '%A«%';

But maybe your collation does not allow the character 'ë'. In that case you'd have to change the column definition first. E.g.

alter table dbo.table
  alter column customvalue nvarchar(1000) collate latin1_general_cs_as;

3 Answers3