checking for numeric value in INSERT statement 1

[NCYankee1]

I am inserting data from an XML string into a SQL Server 2000 table. I have no way to assure that the data I assume is numeric coming in via XML is in fact numeric. So it is causing my insert to fail. Here is the insert statement:

INSERT into sm_InvBalanceRoot
     (LocCd,
      CatCd,
      BookQty)
SELECT 
      LocCd,
      CatCd,
      BookQty
FROM OPENXML (@xmlHandle, '/Record') WITH
  (LocCd varchar(20) './LocCd',
   CatCd varchar(20) './CatCd',
   BookQty varchar(20) './BookQty')

BookQty is defined in my table as an integer (I can't change the table definition) and the BookQty coming in from the XML string is supposed to be numeric, but apparently isn't always numeric. Is there a way I can put a conditional statement (if, case, etc) in the insert statement to say if it is numeric then use the value otherwise insert a 0 into BookQty. (Note: The above insert statement is abbreviated. There are actually a bunch of numeric fields that I need to check, which is why I was wondering if I could check right in the insert statement instead of doing something like inserting the data into a temp table and checking there).

Thanks
-Mike

 

[jbenson001]

Have you tried using IsNumeric()?

Jim

 

[SQLDenis]

ISNUMERIC doesn't always work for example 1D2 is returned as 1 instead of 0

run this

DECLARE @S VARCHAR(50)
SET @S = CHAR(9) --- @S NOW CONTAINS THE TAB CHARACTER
SELECT ISNUMERIC(@S), ISNUMERIC(CHAR(9)),ISNUMERIC('1D2'),ISNUMERIC('D')

you have to do

WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

more here (

http://sqlservercode.blogspot.com/2006/01/isnumeric-sql-server-trouble.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Denis ... cool, thanks for the link...

 

[SQLDenis]

Another option is to validate the XML against an XML Schema or DTD beforehand

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[NCYankee1]

I see how you are checking to ensure the data is in fact numeric. That's cool. Is there a way I can incorporate that directly into my INSERT statement so i am either using the incoming BookQty value if it is numeric, otherwise insert a 0?

INSERT into sm_InvBalanceRoot
     (LocCd,
      CatCd,
      BookQty)
SELECT 
      LocCd,
      CatCd,
      {[i]BookQty or 0[/i]}
....

 

[SQLDenis]

Sure take a look at this

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'


select VALUE,CASE WHEN ISNUMERIC(BookQty ) = 1 AND BookQty NOT LIKE '%[a-z]%' THEN BookQty else '0' end BookQty 
from #foo

so you would do
CASE WHEN ISNUMERIC(Value) = 1 AND Value NOT LIKE '%[a-z]%' THEN VALUE else '0' end value
from ....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

your statement would be

CASE WHEN ISNUMERIC(BookQty ) = 1 AND BookQty NOT LIKE '%[a-z]%' THEN BookQty else '0' end BookQty
from ....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[NCYankee1]

Thank you!!! That worked! I knew you were giving me the answer in your earlier posts. but I was feaking too much to figure out how put it into the proper syntax. Thanks!