|
|
Posted by Hugo Kornelis on 02/02/06 23:35
On 2 Feb 2006 10:04:18 -0800, jim_geissman@countrywide.com wrote:
>I have data tables that include ZIP code, as char(5). The values look
>like integers, but they are padded with leading zeroes to fill out 5
>characters, '00234'.
>
>There are SPs to look up data, with @Zip char(5) among the parameters.
>Some users call these with integer values, @Zip = 234, and SQL makes
>the conversion. Is it necesary to add the leading zeroes in the SP --
>@Zip=RIGHT('00000'+@Zip,5) -- or would SQL find this match? (234 =
>'00234'). It looks like the conversion is to '234' and the match
>fails.
Hi Jim,
Short answer: Never rely on implicit conversion.
Long answer: if @Zip is an integer and the column is char(5), then for a
comparison, the char(5) data in the column will be converted to integer
(look up "data type precedence" in Books Online). This is bad for
several reasons:
1. Unexpected data in the column might cause errors, causing the query
to be aborted.
2. The values in all rows have to be converted, which is slow.
3. If an index on the zip column exists, it can't be used because the
data has to be converted.
So you should definitely ensure that the parameter is converted to the
exact same datatype as the column (i.e. CHAR(5)) before comparing.
And yes - in string comparisons, '00234' is not the same as '234'.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|