Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt

  • 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

  • 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

  • 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

  • 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].

  • 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