SQL Server 2005 Case within Insert Select

What is the syntax to convert a string to int if the string contains a numeric value,  while inserting a row in a table?

Need to do a convert within a select and also a test for numeric?

thanks
ChiBellaAsked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
The case :)
  ,CASE  WHEN  ISNumeric([SOMESTRING4]) = 0 then 0
          ELSE cast([SOMESTRING4] as int) end
or
       ,CASE  WHEN  ISNumeric([SOMESTRING4])=1 then
   cast([SOMESTRING4] as int) ELSE 0 end
from SOMETABLE

 

  SELECT RTRIM(SOMEID)
      ,RTRIM([SOMESTRING1])
      ,RTRIM([SOMESTRING2])
      ,RTRIM([SOMESTRING3])
      ,CASE  WHEN  ISNumeric([SOMESTRING4])=0 then 0
          ELSE cast([SOMESTRING4] as int) end
from SOMETABLE

Open in new window

0
 
dirknibleckCommented:
I think:

CASE WHEN ISNUMERIC(num) THEN CAST(num as Int) ELSE num END

should work...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should test in your client application to make sure that the value for a int (numerical) column is indeed int, and come back to the user to correct the error. it should NOT be done in the sql itself.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ChiBellaAuthor Commented:
Tried this statement but generates error...

SELECT
CASE [STRING_FIELD] WHEN  ISNumeric(RTRIM([STRING_FIELD])) then 0
                  ELSE CONVERT(int, RTRIM([STRING_FIELD]))
                  end
from SOMETABLE

0
 
ErnariashCommented:
Your question to do a convert within a select and also a test for numeric:
declare @variable nvarchar(20)
set @variable= '444'

select cast(@variable as int)
where isnumeric(@variable)=1
Please as angelIII recommended you are not going to get any error on client application....

select cast(@variable as int) 
where isnumeric(@variable)=1

Open in new window

0
 
ChiBellaAuthor Commented:
This doesn't work either


    SELECT
        CASE WHEN ISNUMERIC(string_field)= 0 THEN CAST(string_field as Int) ELSE string_field END
from sometable

Msg 156, Level 15, State 1, Line 80
Incorrect syntax near the keyword 'from'.

0
 
ErnariashCommented:
try

SELECT 
CASE  WHEN  ISNumeric(RTRIM([STRING_FIELD]))=0 then 0
                  ELSE CONVERT(int, RTRIM([STRING_FIELD])) 
                  end
from SOMETABLE

Open in new window

0
 
ChiBellaAuthor Commented:
No, I'm trying to build the statement from a Select statement, not a variable.
0
 
ChiBellaAuthor Commented:
Msg 156, Level 15, State 1, Line 80
Incorrect syntax near the keyword 'from'.
0
 
ErnariashCommented:
your could get errors
On the case the then and else have to be the same data type....you are going to use 0 as shows here
 

   SELECT 
        CASE WHEN ISNUMERIC(string_field)= 0 THEN CAST(string_field as Int) ELSE 0 END 
from sometable 

Open in new window

0
 
ChiBellaAuthor Commented:
tried that still get

Msg 156, Level 15, State 1, Line 79
Incorrect syntax near the keyword 'from'.
0
 
ErnariashCommented:
Are u using SQL Server 2005, please post your full query there is nothing wrong with the case statement I am testing it with my tables zip codes...it works.
SELECT [PCP_Zip], 
CASE  WHEN  ISNumeric(RTRIM([PCP_Zip]))=0 then 0
                  ELSE CONVERT(int, RTRIM([PCP_Zip])) 
                  end
from [EMPMEDWEB].[dbo].[Member_Insurance]

Open in new window

0
 
ErnariashCommented:
An extra comment you do not need to use RTRIM or LTRIM with ISNumeric, CONVERT or CAST.....

SELECT CASE  WHEN  ISNumeric(PCP_Zip)=0 then 0
                  ELSE CAST([PCP_Zip] as int) 
                  end
from YOURTABLE

Open in new window

0
 
ChiBellaAuthor Commented:
   SELECT RTRIM(SOMEID
      ,RTRIM([SOMESTRING1])
      ,RTRIM([SOMESTRING2])
      ,RTRIM([SOMESTRING3])
      ,CASE  WHEN  ISNumeric(RTRIM([SOMESTRING4]))=0 then
         cast(RTRIM([SOMESTRING4]) as int) ELSE 0 end
from SOMETABLE

generates error...
0
 
ErnariashCommented:
Missing ) :)

  SELECT RTRIM(SOMEID)
      ,RTRIM([SOMESTRING1])
      ,RTRIM([SOMESTRING2])
      ,RTRIM([SOMESTRING3])
      ,CASE  WHEN  ISNumeric([SOMESTRING4])=0 then 
         cast([SOMESTRING4] as int) ELSE 0 end
from SOMETABLE

Open in new window

0
 
ChiBellaAuthor Commented:
I corrected the errors but still get the "from keyword" error...

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'from'.

    SELECT RTRIM(SOMEID
      ,RTRIM([SOMESTRING1])
      ,RTRIM([SOMESTRING2])
      ,RTRIM([SOMESTRING3])
      ,CASE  WHEN  ISNumeric([SOMESTRING4])=0 then 
	   cast([SOMESTRING4] as int) ELSE 0 end
from SOMETABLE

Open in new window

0
 
ErnariashCommented:
  your are missing clossing the )  ....SELECT RTRIM(SOMEID
SELECT RTRIM(SOMEID)
0
 
ChiBellaAuthor Commented:
OK, I was missing a ")" after SOMEID. I fixed that, but now all values are "0" for somestring4.
0
 
ChiBellaAuthor Commented:
ok, got it... isnumeric should be 1, not 0.

thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.