Skip to content

Varchar vs Char watchout

July 25, 2007

This happens all the time in the world of developers. You’ve been working with a technology for so long you think you know everything (enough) about it. Then suddenly, poof, something’s not working the way you expect it to. Before you go crazy and reassess all your previous understanding, calm done. My usually approach is to simplify things and do some experiments. This works all the time.

My problem: I was trying to pad a number with zeros and make it a fixed width but it wasn’t working.

This was my original non-working code:
1. select RIGHT((‘0000’ + CAST(num1 as char(4))), 4) from #TEMP_TBL1 

This code works:
2. select RIGHT((‘0000’ + CAST(num1 as varchar(4))), 4) from #TEMP_TBL1

See the difference? Yes, in the working snippet I cast the number to a varchar. The trouble with Char is that it right-gets padded with spaces if the number is less than the specified width. d’oh! I realized this during my trip home on the subway. My other false assumptions were that I wasn’t using the RIGHT function correctly, something was wrong with my concatenation, etc.

My experiments didn’t shed much light into the problem because in trying to simplify things, I removed the CAST, which perplexed me further.

select RIGHT((‘00002’), 4) from #TEMP_TBL1 select RIGHT((‘0000’ + ‘8’), 4) from #TEMP_TBL1With the benefit of hindsight, this test makes it clear what the difference is in using varchar vs char.select (‘0000’ + CAST(num1 as varchar(4)) + ‘0’) from #TEMP_TBL1select (‘0000’ + CAST(num1 as char(4)) + ‘0’) from #TEMP_TBL1

Advertisements

From → sql

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: