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