Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links




Post Categories

CHAR(0) is not that innocent you may think

Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was

declare @s varchar(100)

set @s = 'xxxx                                  yyyyy          zzzzzz'

SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '')

I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen.

Run this on your own risk, as you will see soon.

Select  q,
from    (
            SELECT    REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) + ' ', ''), CHAR(0), '') as q
        ) AS d

After some investigation there was a follow up with another twist of same problem.
As before, run this on your own risk. Your query window might go into an infinite loop.

select replace(cast('x' as varchar(max)), char(0), '')

Not all get this behaviour. It seems it is dependent on which collation you use, as shown here

-- Success
select replace(cast('x' COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(max)), char(0), '');

-- Hangs
select replace(cast('x' COLLATE Latin1_General_CI_AS as varchar(max)), char(0), '');

And it doesn't happen for VARCHAR(n) nor NVARCHAR(MAX).

Read more about this on Connect here

Print | posted on Sunday, August 23, 2009 9:33 AM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 Miscellaneous ]



# re: CHAR(0) is not that innocent you may think

Here, I used series of special characters instead of char(2)
8/24/2009 12:31 PM | Madhivanan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET