SQL Tidbits #2 – Default Varchar Length

It is a pretty common thing to have to convert one datatype to another datatype. When converting to varchar it would look like the following.

SELECT CONVERT(VARCHAR(30), 12345)

This would convert the integer value 12345 into a string. Maybe it’s for a formatting reason or maybe you need to concatenate it with another string. Something of interest is that you can get lazy and use this snippet.

SELECT CONVERT(VARCHAR, 12345)

In this example you will see absolutely no difference in the results. However, this laziness can create problems. Leaving off the length does not make the length the same as anything that happens to be converted. It defaults to 30. So what would happen with this example?

SELECT CONVERT(VARCHAR, 'The young woman unzipped the carrying case for her little dog.')

There would not be an error and the return value would be “The young woman unzipped the c”, which is quite an interest start to a sentence but not the intended result. The moral of the story is to always explicitly enclose your length.

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *