string to int????

string to int????

• Mark Shoesmith

  Create a UDF to do this and use isnumeric to check before convert.
  
  
  

• Gary Lam

  Hi Germn,

   

  You'll have to use a CASE expression to test for non-numeric characters, then return a fixed value if any are found.

   

  For example (assuming only positive integers are valid):

   

  CASE WHEN MyColumn LIKE '%[^0-9]%' THEN 0 ELSE CAST(MyColumn AS int) END

  
  --
  Hugo Kornelis, SQL Server MVP

   

  <Germn Schuager@discussions.microsoft.com> schreef in bericht news:41ac2dc5-fe2c-4b85-bd49-2ee6dd1ad09c@discussions.microsoft.com...

  Hi, I need to convert a string to an integer.

  I've tried using CAST and CONVERT but the problem is that my data also contains alphanumeric characters, in wich case I want the result of the conversion to be 0.

  The above commands gives an error if the data is not pure numeric.

  What can I do

  
  

• HansBaltussen

  What do you WANT to do   What's your expected output if there are alpha characters mixed in

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <German Schuager@discussions.microsoft.com> wrote in message news:41ac2dc5-fe2c-4b85-bd49-2ee6dd1ad09c@discussions.microsoft.com...

  Hi, I need to convert a string to an integer.

  I've tried using CAST and CONVERT but the problem is that my data also contains alphanumeric characters, in wich case I want the result of the conversion to be 0.

  The above commands gives an error if the data is not pure numeric.

  What can I do

  
  

• david hug

  Here is a quick example.
  
  select case when isnumeric(i)=1 then cast(i as int) else 0 end
  from(
  select '123' i
  union all select 'abc123'
  )tb
  
  
  
  
  

• Hardik Mehta

  I've solved the problem with these stament:

  (CASE WHEN ISNUMERIC(x.p1)=1 THEN CAST(x.p1 AS int) ELSE 0 END)

  Thanks.

  
  
• string to int????