Query Result set explanation needed

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  January 28, 2015 at 6:22 pm

  #397942

  Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?

  create table #temp

  (

  code varchar(4) null,

  id int not null

  )

  insert into #temp

  select '0123',1

  union all

  select '1232',2

  UNION ALL

  select '0423',3

  union all

  select '1242',4

  select CONVERT(numeric(4,0),code) as code,ID

  into #temp1

  from #temp

  select * from #temp a

  INNER JOIN #temp1 b

  on a.code = b.code

  its considering the record 0123 from temp table to 123 temp1 as same .why is that ?

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 28, 2015 at 6:58 pm

  #1773864

  It's because your varchar column is being implicitly converted to a numeric(4,0) as stated on the data type precedences. SQL Server does implicit conversions because it can't compare apples with oranges (different data types).

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  January 28, 2015 at 7:06 pm

  #1773865

  Thanks Luiz for the info.

  One last question on that why doesnt it fail when inserting a numeric field value to varchar() field?

  Insert into #temp

  Select * from #temp1

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• Xedni

  SSCertifiable

  Points: 6951

  More actions

  January 28, 2015 at 8:52 pm

  #1773872

  It's just casting the number to a string. You can make any number into a string, but you can only turn valid number strings into numbers.

  Executive Junior Cowboy Developer, Esq.[/url]

• Bhushan Kulkarni

  SSCrazy

  Points: 2829

  More actions

  January 28, 2015 at 10:00 pm

  #1773877

  SQL Server will implicitly cast varchar '0123' to numeric 123.