Always make sure you specify the length to CONVERT in SQL

Over the years I've seen this potential blackhole of data loss several times, a developer has written CONVERT(VARCHAR, [ColumnName]) without specifying the length of the VARCHAR to convert the column to. The reasons behind converting the column to VARCHAR are usually an entirely different story, but focusing on the CONVERT, if you run this query:

SELECT	ColumnValue, 
	LEN(ColumnValue) [LengthOfColumnValueColumn], 
	CONVERT(VARCHAR, ColumnValue) [ColumnValueConvertedToVarchar], 
	LEN(CONVERT(VARCHAR, ColumnValue)) [LengthOfColumnValueConvertedToVarchar],
	CONVERT(VARCHAR(200), ColumnValue) [ColumnValueConvertedToVarchar200],
	LEN(CONVERT(VARCHAR(200), ColumnValue)) [LengthOfColumnValueConvertedToVarchar200]
		SELECT 'wfqfhowqfhwioqfhwqiofhwqofhwqiofhwiqofhwioqfhwqiofhwqoifhwqofwqwfqfhowqfh' AS ColumnValue
	) SQ

The outpt you get for each column shows the impact this mistake has. Focusing on just the columns for the length of the columns (LengthOfColumnValueColumn, LengthOfColumnValueConvertedToVarchar and LengthOfColumnValueConvertedToVarchar200), the output is:

  • LengthOfColumnValueColumn: 73
  • LengthOfColumnValueConvertedToVarchar: 30
  • LengthOfColumnValueConvertedToVarchar200: 73

In other words, if you use CONVERT without specifying a length you can say goodbye to anything more than 30 characters of it!

Always, always be explicit about what you're asking for. Any assumption will, well you know the saying...

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment