Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 4:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 9:58 AM
Points: 19, Visits: 71
Can someone help me understand why this is happening:

If I put the following in a where clause:

CASE WHEN PATINDEX('%[^0-9 ]%',h.oe_po_no) = 0
THEN Cast(RTrim(LTrim(h.oe_po_no)) as BigInt)
ELSE 0
END = 0

I get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

I have seen the same situation when using IsNumeric instead of PATINDEX. Why doesn't T-SQL truly recognize numeric strings and allow them to be converted? This is so frustrating.

Thank you.

Tammy
Post #1441522
Posted Thursday, April 11, 2013 5:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:52 PM
Points: 364, Visits: 385
Can you please provide the CREATE TABLE statement of the h table you are referring to, as well as some sample data?

Edit: A small test to get you started anyway. It's probably because of the extra space in your pattern. Remove the '123 456' row from the @test table and see the difference...

declare @test table (po_number nvarchar(50))

insert into @test (po_number)
values ('123 456'),('not a po'),('123456'),('123-456'),('not a po'),('-123456')

select
CASE WHEN PATINDEX('%[^0-9 ]%',po_number) = 0
THEN Cast(RTrim(LTrim(po_number)) as BigInt)
ELSE 0
END
from @test

Post #1441529
Posted Thursday, April 11, 2013 5:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 1,054, Visits: 3,123
I suspect it may be a space within the number, for example this will cause the error

SELECT CAST('123 456' AS BIGINT)

If you want to compress this situation into a single number then you could do
CASE	WHEN PATINDEX('%[^0-9]%',REPLACE(h.oe_po_no,' ','')) = 0 
THEN Cast(REPLACE(h.oe_po_no,' ','') as BigInt)
ELSE 0
END = 0

Otherwise to throw a zero
CASE	WHEN PATINDEX('%[^0-9]%',RTRIM(LTRIM(h.oe_po_no))) = 0 
THEN Cast(RTRIM(LTRIM(h.oe_po_no)) as BigInt)
ELSE 0
END = 0

Post #1441530
Posted Thursday, April 11, 2013 8:39 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
tammyf (4/11/2013)
Can someone help me understand why this is happening:

If I put the following in a where clause:

CASE WHEN PATINDEX('%[^0-9 ]%',h.oe_po_no) = 0
THEN Cast(RTrim(LTrim(h.oe_po_no)) as BigInt)
ELSE 0
END = 0

I get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

I have seen the same situation when using IsNumeric instead of PATINDEX. Why doesn't T-SQL truly recognize numeric strings and allow them to be converted? This is so frustrating.

Thank you.

Tammy


The problem is the space in this: [^0-9 ]. It needs to be this: [^0-9].



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441539
Posted Monday, April 15, 2013 10:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 9:58 AM
Points: 19, Visits: 71
If there is a way to award points or give credit, I don't know what it is, but all three of you helped me with this. I believe it is probably a space within the string of numbers. When I added the Replace of a space with an empty string, it worked.

Thank you.

If there is a way to mark your answers as the answer, please let me know
Post #1442399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse