T-SQL Gotcha

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), '');

GO

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 where CHAR(0) is a defined character the query returns as expected, as below;

DECLARE @var VARCHAR(MAX);

SET @var = 'SQL Server 365';

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

GO

Enjoy!

Chris