April 11, 2013 at 4:56 pm
Can someone help me understand why this is happening:
If I put the following in a where clause:
CASEWHEN 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
April 11, 2013 at 5:47 pm
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-2 table and see the difference...
declare @test-2 table (po_number nvarchar(50))
insert into @test-2 (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-2
April 11, 2013 at 5:58 pm
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
CASEWHEN 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
CASEWHEN PATINDEX('%[^0-9]%',RTRIM(LTRIM(h.oe_po_no))) = 0
THEN Cast(RTRIM(LTRIM(h.oe_po_no)) as BigInt)
ELSE 0
END = 0
April 11, 2013 at 8:39 pm
tammyf (4/11/2013)
Can someone help me understand why this is happening:If I put the following in a where clause:
CASEWHEN 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].
April 15, 2013 at 10:29 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply