Varchar vs Char watchout
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