Conversion failed when converting the nvarchar value to data type int

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  August 9, 2012 at 4:05 am

  #260574

  ---Create Table

  create table tmp_dist

  ([District] nvarchar(140))

  --Insert Into Table

  insert into tmp_dist values ('1030');

  insert into tmp_dist values ('1080');

  insert into tmp_dist values ('9200.');

  insert into tmp_dist values ('9999MiriTest');

  insert into tmp_dist values ('9999MiriTest1545');

  insert into tmp_dist values ('Districts');

  insert into tmp_dist values ('ABC');

  -----------This is my SQL

  select [District],substring([District],0,charindex('.',[District],1)),

  case when ascii(substring([District],1,1)) between 48 and 57 then [District] else 0 end Dist

  from tmp_dist

  I want to show the result as like below....from my above sql.

  But my above sql giving me an error "Conversion failed when converting the nvarchar value '9200.' to data type int."

  So request you to please provide me solution how to handle this case.How would be my sql

  Expected Result

  -------------------

  District

  -----------

  1030

  1080

  9200

  9999

  9999

  0

  0

  Regards,

  KRaj

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  August 9, 2012 at 5:04 am

  #1523670

  Just put the 0 in quotes...

  select [District],substring([District],0,charindex('.',[District],1)),

  case when ascii(substring([District],1,1)) between 48 and 57 then [District] else '0' end Dist

  from tmp_dist

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  August 9, 2012 at 6:02 am

  #1523697

  thanks for your valuable reply,but whats about my other result....

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  August 9, 2012 at 7:53 am

  #1523788

  Seems to be a problem with the BETWEEN part of the query; it can't do a comparison to those numbers because you're passing an nvarchar type to the BETWEEN, and it needs dates or numbers to work with. This should fix that up:

  select [District],substring([District],0,charindex('.',[District],1)),

  case when CAST(ascii(substring([District],1,1)) AS int) between 48 and 57 then [District] else '0' end Dist

  from tmp_dist

  However, this doesn't quite return the results you wanted; namely, the 4th and 5th rows have more than just the District number. Is this the expected output, or should it be trimmed further?

  - 😀