Wednesday, March 11, 2015

[WAT] Fun length issue with REPLACE in SSMS "Results to"

Just had this burn me, and don't remember having seen this issue before.

REPLACE changes the length of your columns, in Results to Text/Results to File.

Run this piece of code in SSMS. Query->Results To->Grid.
SELECT TOP 100 REPLACE(REPLACE(name,'&',';'),'/','#'), name FROM sysdatabases

Copy and paste on successive lines.  The same.
Now do the same thing, in Results to Text...
And now the same, Results to File...

Note that they're no longer the same length.  Seems the case at least since 2005.
The length of the header, which dictates the length (if doing fixed-width stuff) is twice as long.  That occurs even when the replaced characters are the same length.

Now let's see what our 2012++ friend, which gives us the metadata for a query, says.

SELECT * FROM sys.dm_exec_describe_first_result_set('
SELECT TOP 100 replace(replace(name,''&'','';''),''/'',''$'') as rname, name
FROM sysdatabases', NULL, 0)

Which gets you this fun nugget...

Hey look!  It went from nvarchar(128) (aka sysname) to nvarchar(4000).  Interesting!

Maybe this is normal/expected behavior.  But it made life difficult today, and experience is gained from doing it wrong, so here's your experience for the day.

No comments: