CODEWITHSUNDEEP
t-sqlunicodereplace
Rebecca
Rebecca

Reputation: 14402

Replacing an invisible unicode control character in TSQL

I'm trying to search and replace an invisible unicode control character in a string in TSQL. The control character is 'LSEP' or 0x2028.

I can find the character easily enough using either of these two queries:

SELECT * FROM Email WHERE Html LIKE '%[0x2028]%'

or

SELECT * FROM Email WHERE CHARINDEX(NCHAR(0x2028) COLLATE Latin1_General_BIN2, Html) > 0

However, when I come to try and replace it, the following just doesn't work:

UPDATE Email
SET Html = REPLACE(Html, NCHAR(0x2028) COLLATE Latin1_General_BIN2, '')
WHERE Html LIKE '%[0x2028]%'

Any ideas what I'm doing wrong. I can't use the character itself using N'LSEP', because it just appears as a newline in the script when I try and paste it!

Sample input, as requested:

</span><span>
</span><span>

Upvotes: 0

Views: 803

Answers (1)

bastos.sergio
bastos.sergio

Reputation: 6764

Try this (it replaces the unicode LSEP with the unicode SPACE char)...

UPDATE Email
SET Html = REPLACE(Html, NCHAR(0x2028), NCHAR(0x0020))
WHERE Html LIKE '%[0x2028]%'

Upvotes: 1

Related Questions