[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

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
0
ChiBella
Asked:
ChiBella
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
ChiBellaAuthor Commented:
ok, got it... isnumeric should be 1, not 0.

thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now