Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

September 26, 2012

A Quick SQL Challenge

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:21 pm

So I was recently involved in an issue where a particular Unicode string was causing some problems in an application. The string was coming from a production database. I was asked to help reproduce this. It boiled down to this:

Can you write a SQL Script which produces a string containing every possible unicode character?

That’s every character from 0x0000 (NUL) all the way up to 0xffff (which is not even legal unicode). If you want a crack at it, start with the code here:

declare @allNCHARS nvarchar(max);
 
/*
Your answer would fit here.
*/
 
UPDATE MYTABLE 
SET Value = @allNCHARS
WHERE Id = 1;

My own answer is in the comments Maybe you can come up with something more elegant.
But it’s a handy string. If you’re in QA, it can help exercise a lot of different text issues (code pages, encodings, conversions etc…).

In my own case, the string actually helped me. I was able to reproduce the issue on a local developer machine. A colleague of mine then did more detective work and squashed that bug, so it was a bit of a tag team effort there.

6 Comments »

  1. Here’s my try:

    declare @allNCHARS nvarchar(max);
 
-- my try:
set @allNCHARS = N'';
 
declare @i int = 0;
while @i <= 0xffff
begin
	set @allNCHARS += NCHAR(@i);
	set @i += 1;
end
 
UPDATE MYTABLE 
SET Value = @allNCHARS
WHERE Id = 1;

    Comment by Michael J. Swart — September 26, 2012 @ 9:22 pm

  2. Comment tip: wrap comments in <pre lang=”tsql”> and </pre>

    Comment by Michael J. Swart — September 26, 2012 @ 9:22 pm

  3. With a simple ‘Numbers’ tables you could use the following approach:

    update MYTABLE
set    Value = (select nchar(n)
                from   Numbers
                where  n between 0 and 0xffff
                for xml path(''))
where  Id = 1;

    The flaw is that some chars will be escaped (e.g. &#x0D or <).

    The following one didn’t work (but I don’t know why):

    update  MYTABLE
set     Value =	''
where   Id = 1;
 
update	MYTABLE
set     Value += nchar(n)
from    MYTABLE
cross join Numbers
where   Id = 1
    and n between 0 and 0xffff;

    Comment by Manfred Sorg — September 27, 2012 @ 1:30 am

  4. Thanks for posting Manfred… I love the idea of the numbers table. It sounds promising: Here’s an example of how to generate one.

    I couldn’t get the FOR XML technique to work either. Even using “,TYPE” as mentioned in this stackoverflow question. It chokes on a few characters which are apparently not allowed in xml.

    You’re second idea almost works. I think that we need the row to be updated multiple times as SQL Server processes each character. But that’s not the case because it doesn’t behave like a loop. If I find a solution using Numbers, I’ll post it.

    Comment by Michael J. Swart — September 27, 2012 @ 8:57 am

  5. — variation on Manfred’s second solution which updates the variable instead of the table. (requires Table Numbers with field Number.)

    select @allNCHARS=''
 
select @allNCHARS=@allNCHARS+nchar(Number)
FROM Numbers
where   Number between 0 and 0xffff;
 
select @allNCHARS

    Comment by david mckinney — November 2, 2012 @ 10:15 am

  6. Hi David,
    Your answer – I believe – is the definitive one. I like it for two reasons:
    * It is extremely simple and clear (and avoids any XML stuff)
    * It’s an opportunity to point out a legitimate use-case for a numbers table.

    Comment by Michael J. Swart — November 2, 2012 @ 11:16 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress