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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
ErnariashCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChiBellaAuthor Commented:
ok, got it... isnumeric should be 1, not 0.

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.