How to identify and remove the SQL table row which contains UTF 8 BOM characters 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?
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);