T-SQL Gotcha

By Chris McGowan, 2013/02/15

I came across an interesting issue a few days ago that I thought I would blog about. The issue is replacing NUL characters from values in SQL Server, more specifically using REPLACE(). Take the below TSQL which runs but will never complete;

DECLARE @var VARCHAR(MAX);

SET @var = 'SQL Server 365';

SELECT REPLACE(@var, CHAR(0), '');

NOTE – Do not run this on production it will never complete and will ramp up CPU!

Why? Well, this is due to the fact that CHAR(0) (or 0x0000 in ASCII) is an undefined character in Windows collations and all undefined characters are ignored during comparison, sort, and pattern matching so the query effectively gets stuck in an infinite loop!

If we COLLATE the string to a SQL Collation whereCHAR(0) is a defined character the query returns as expected, as below;

DECLARE @var VARCHAR(MAX);

SET @var = 'SQL Server 365';

SELECT REPLACE(@var COLLATESQL_Latin1_General_CP1_CI_AS, CHAR(0), '');

Enjoy!

Chris