select len(cast([some sql_variant] as varchar))

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select len(cast([some sql_variant] as varchar))

Author

Topic

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-09-16 : 11:32:55

I was presented with a query this morning that was returning invalid results.


select len(cast([some sql_variant] as varchar))

The problem was that even though there were values greater than 3,000 characters in the sql_variant field, the len function returned either 29 or 30 for the string. Immediately, I specified a length for the cast statement and that was a valid solution.


select len(cast([some sql_variant] as varchar(4000)))

I tried to explain why and found myself at a slight loss. I have no idea why I got 29 sometimes and 30 sometimes. I have no idea where or how SQL decided the max length for that value.

I did some research and found these two articles on BOL and took some excerpts. I don't think they address the issue 100%.

Can someone please clarify?

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Implicit Conversions
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

http://msdn.microsoft.com/en-us/library/ms191530.aspx#_character
Converting Character Data
When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-16 : 15:34:28

cast and convert will default the length to 30 when it is not specified.
The conversion will truncate the value to the conversion target length.


select len(convert(varchar, replicate('-', 3000)))
select len(convert(varchar(300), replicate('-', 3000)))
select len(convert(varchar(6000), replicate('-', 3000)))

output:

-----------
30

-----------
300

-----------
3000

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-17 : 02:21:05

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-09-24 : 17:11:10

Much appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources