Select and filter nvarchar like a int

Question

I have a nvarchar column BigMacs in table McTable in my MS SQL 2005 database with alfanumeric and numeric values. For example:

• 132
• 432adfad
• sfs54543
• 5256

And now i would like to do something like this:

select Convert(BigMacs, int) from McTable
where IsNumerc(BigMacs) = 1 AND Convert(BigMacs, int) > 6

But when I do this i get a error:

Msg 245, Level 16, State 1, Line 41 Conversion failed when converting the nvarchar value '.' to data type int.

On line select.

How to fix this?

Answer 1

Try this:

SELECT  *
FROM    (
        SELECT  REPLACE(BigMacs, ',', '.') AS BigMacs
        FROM    McTable m
        WHERE   IsNumerc(BigMacs) = 1
        ) q
WHERE   CAST(BigMacs AS DECIMAL) > 6

IsNumeric will return TRUE on decimal fractions like 1234.1232, but they cannot be converted to INT.

Checking:

WITH McTable AS
        (
        SELECT '123124,123123' AS BigMacs
        )
SELECT  *
FROM    (
        SELECT  REPLACE(BigMacs, ',', '.') AS BigMacs
        FROM    McTable
        WHERE   IsNumeric(BigMacs) = 1
        ) q
WHERE   CAST(BigMacs AS DECIMAL) > 6

-----------
123124.123123

Answer 2

This is probably because the IsNumeric function returns true for any value that COULD be converted to a number. Try the following example:

create table McTable  (BigMac varchar(255))

insert into McTable  select '1p927'
insert into McTable  select '1927'
insert into McTable  select '1,927'
insert into McTable  select '1.927'

select BigMac, isnumeric(BigMac)
from McTable 

select BigMac, CAST(BigMac AS DECIMAL)
from McTable 
where isnumeric(BigMac) = 1

Even though all rows except the '1p927' are numeric, the cast will fail! This is because '1,927' cannot be converted to a Decimal (on my machine)

IsNumeric doesn't work exactly as specified. As found here, you could use

IsNumeric (data + 'e0')

-Edo

Answer 3

There are many ways to accomplish this. Both of these work the same. It is best not to use replace in this situation as there are too many unknowns to account for to replace. It is best to filter everything that is NOT what your after.

SELECT
    CONVERT(INT,BigMacs) AS BigMacs
FROM
    McTable
WHERE
    ISNUMERIC(BigMacs) = 1
AND PATINDEX('%[^0-9]%', BigMacs) = 0


SELECT
    CONVERT(INT,BigMacs) AS BigMacs
FROM
    McTable
WHERE
    ISNUMERIC(BigMacs) = 1
AND BigMacs NOT LIKE ('%[^0-9]%')

Note: It helps if people spell ISNUMERIC() correctly. It also helps if you use the correct syntax order on CONVERT()