Search and Replace Byte Order Mark In Sql Server

Question

i have 10 table and more than 10000 record that contain  how can search  and replace this in DB? since the  equal 0xEF,0xBB,0xBF how can search this?

i use this code

 WITH foo(myvarbincolumn) AS
(
SELECT text from BPM_Letters
)
SELECT *
FROM   foo
WHERE  CONVERT(VARCHAR(max), myvarbincolumn) COLLATE Arabic_CI_AS
                    LIKE '%' + CONVERT(NVARCHAR(max), CHAR(0xEF)+CHAR(0xBB)+CHAR(0xBF)) + '%' 

I found this code in stackoverflow but it s incomplete.

script of BPM_Letters this code not find any record! please help me

Answer 1

I wrote a query to find that weird character via the query below:

SELECT cast(LEFT(text,1) AS VARBINARY(MAX)) from BPM_Letters  

and the result was 0xFFFE. So I wrote this query and it worked perfectly:

UPDATE BPM_Letters Set text=REPLACE(text,0xFFFE,'');

Answer 2

What about this CTE:

StripBOM AS
(
   SELECT CASE
             WHEN LEFT(text,3) = 0xEFBBBF
                THEN CONVERT(varbinary(max),SUBSTRING(text, 4, LEN(text)))
             ELSE text             
           END AS text
   FROM BPM_Letters
)

It should provide you with a new table where all BOM characters have been stripped off.

P.S. This code assumes 'text' field is of type varbinary.

Answer 3

Here's a simpler answer that builds on the other ones:

UPDATE BPM_Letters SET text=substr(text, 4) WHERE left(text, 3) = 0xEFBBBF;

I've tested this, and it works.