string to INTGER conversion

• Unnikrishnan.C

  Old Hand

  Points: 320

  More actions

  February 21, 2005 at 7:14 am

  #65658

  Hi,

  Is there any function in MS SQL server 2000 to convert a string value to an equivalent integer  value? ( i.e. an equivalent of  atoi() in C languge defined in <stdlib.h&gt

  Or How can I store a hexadecimal number in  SQL server and use them in comparison operations like the one given below:

  > CREATE TABLE NumTest (StartNumber varchar(10),EndNumber varchar(10))

  >INSERT INTO NumTest  (StartNumber, EndNumber ) VALUES (100,199)

  >INSERT INTO NumTest  (StartNumber, EndNumber ) VALUES (200,299)

  >INSERT INTO NumTest  (StartNumber, EndNumber ) VALUES (3FF,5FF) -- 3rd row is having hexadecimal values.

  When I run the following query I am getting the result with error:

  >SELECT * FROM NumTest WHERE CAST(StartNumber AS INT) >100

  StartNumber EndNumber 

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

  200         299

  Server: Msg 245, Level 16, State 1, Line 1

  Syntax error converting the varchar value '3FF' to a column of data type int.

  Actually I need to get the row with 3FF..

  Thanks in advance.

  Unnic

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 21, 2005 at 7:29 am

  #543315

  Lots of useful ideas here:

  http://groups.google.de/groups?hl=de&lr=&threadm=119001c4a5c5%24c140b440%24a601280a%40phx.gbl&rnum=1&prev=/groups%3Fq%3Dconvert%2Bfrom%2Bhexadecimal%2Bto%2Ba%2Bdecimal%26hl%3Dde%26lr%3D%26selm%3D119001c4a5c5%2524c140b440%2524a601280a%2540phx.gbl%26rnum%3D1

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]