SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 06:16 PM
| ||||
| ||||
 Int to char conversion 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. Thanks, Jim Geissman Countrywide      |
|
02-29-2008, 06:16 PM
| |||
| |||
| Re: Int to char conversion jim_geissman@countrywide.com wrote: >It looks like the conversion is to '234' and the match > fails. Doesn't that answer your question? |
|
02-29-2008, 06:16 PM
| |||
| |||
| Re: Int to char conversion Not sure exactly what your questiuon is ...but you may want to post your could...might be helpful MJKulangara http://sqladventures.blogspot.com |
|
02-29-2008, 06:17 PM
| |||
| |||
| Re: Int to char conversion 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 |
|
02-29-2008, 06:17 PM
| ||||
| ||||
| Re: Int to char conversion (jim_geissman@countrywide.com) writes: > 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. Yes, you would need to pad the input parameter with leading zeroes. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
 |
« Database views hierarchy
|
help »
| Thread Tools | |
| Display Modes | |
Linear Mode
|
|
All times are GMT. The time now is 12:44 PM.
