1

I recently identified that some of the rows in our table contains UTF 8 BOM (Byte Order Marking) characters ().. Is there a way to identify those rows and removing the BOM characters from those rows?

3

Balu, the answer you posted will truncate the value to 30 characters because the no length was specified for the CAST and CONVERT declarations of the varchar and nvarchar data types.

Consider using STUFF instead:

--nvarchar example
UPDATE dbo.YourTable
SET UnicodeColumn = STUFF(UnicodeColumn, 1, 1, '')
WHERE CAST(LEFT(UnicodeColumn, 1) AS binary(2)) IN(0xFFFE, 0xFEFF);

--varchar example
UPDATE dbo.YourTable
SET NonUnicodeColumn = STUFF(NonUnicodeColumn, 1, 2, '')
WHERE CAST(LEFT(NonUnicodeColumn, 2) AS binary(2)) IN(0xFFFE, 0xFEFF);
| improve this answer | |
0

Finally, got it:

select *, SUBSTRING(CAST(CAST(stringValue AS varchar) AS nvarchar), 2, LEN(stringValue))
from test_table
where fileNumber=102 and CAST(left(stringValue,1) AS varchar)='?';

update test_table
set stringValue=SUBSTRING(CAST(CAST(stringValue AS varchar) AS nvarchar), 2, LEN(stringValue))
where fileNumber=102 and CAST(left(stringValue,1) AS varchar)='?';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.