Select and filter nvarchar like a int

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?

From stackoverflow

• 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
  

  tomaszs : This is not working (condition in select?). And when I move it to where i get error: Error converting data type nvarchar to numeric., but when I get select IsNumeric(BigMacs) I get all 1.

  Quassnoi : What exactly are you trying to SELECT? What does this '> 6' condition mean? Are you trying to select all applicable rows and find out if they are greater than 6, or to filter out those rows less than 6?

  tomaszs : I would like to get rows from McTable that have numeric value in BigMacs and that this value is greater than 6

  Quassnoi : See updated post

  tomaszs : Now i get Error converting data type nvarchar to numeric. on Select * from first line.

  edosoft : I'm sorry but this solution will not work. See my post for an explanation

  tomaszs : But what is the solution ?

  Quassnoi : This seems to be locale dependent. You'll need to replace a comma with a period. See updated post.

  tomaszs : Yes, but I dont have any period and any dot in my column.

  edosoft : My point is that IsNumeric() returns 1 for values that cannot be cast to a decimal. You cannot rely on it.

  Quassnoi : Could you please locate the rows that fails? Try adding TOP N to your query, increasing N, and locate that very row that fails.

  tomaszs : I've tried it. And it came out that the value is 44 and it occurs 3 times in this column.

  Quassnoi : Could it please post CAST(BigMacs AS BINARY) for this row?

  tomaszs : I've did it, and value is : 0x340034000000000000000000000000000000000000000000000000000000

  edosoft : Google found a hack: Use isnumeric(BigMac + 'e0'). See my edited answer

  tomaszs : You are right Edoode, thank you!

• 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

  tomaszs : Ok, but when I select from select still I get this error (like Spencer Ruport)

  tomaszs : I don't have coma or dot in my values.

  edosoft : My point is that IsNumeric() returns 1 for values that cannot be cast to a decimal. You cannot rely on it.

  tomaszs : I've added this function and did this: select * from (select BigMac, CAST(BigMac AS DECIMAL) ValueS from McTable where dbo.isReallyNumeric (BigMac) = 1) tablee WHERE tablee.ValueS > 5 But still I get: Error converting data type nvarchar to numeric.

  edosoft : @tomaszs: you have some weird values in your table :)

  tomaszs : You saved my day Edoode!

• 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()