How to filter nvarchar more than a numeric value?

Question

I have an MS SQL McTable table with a BigMacs nvarchar (255) column. I would like to get rows with a BigMacs value greater than 5.

What am I doing:

select * from ( select BigMacs BigMacsS, CAST(BigMacs as Binary) BigMacsB, CAST(BigMacs as int) BigMacsL from McTable where BigMacs Like '%[0-9]%' ) table where Cast(table.BigMacsL as int) > 5 

And as a result, I get an error message:

State 1, line 67 Conversion failed while converting nvarchar value "***" - int data type.

But when I remove the last filter where Cast(table.BigMacsL as int) > 5 , it works, and I get this result:

  6 0x3600000000000000000000000000000000000000000000000000000000000000 6
 23 0x3200330000000000000000000000000000000000000000000000000000000000 23
 22 0x3200320000000000000000000000000000000000000000000000000000000000 22
 24 0x32003400000000000000000000000000000000000000000000000000000000 24
 25 0x3200350000000000000000000000000000000000000000000000000000000000 25
 3 0x3300000000000000000000000000000000000000000000000000000000000000 3
 17 0x3100370000000000000000000000000000000000000000000000000000000000 17
 17 0x3100370000000000000000000000000000000000000000000000000000000000 17
 19 0x31003900000000000000000000000000000000000000000000000000000000 19
 20 0x320030000000000000000000000000000000000000000000000000000000 20
 659 0x36003500390000000000000000000000000000000000000000000000000000 659
 1 0x3100000000000000000000000000000000000000000000000000000000000000 1
 43 0x3400330000000000000000000000000000000000000000000000000000000000 43
 44 0x34003400000000000000000000000000000000000000000000000000000000 44
 45 0x3400350000000000000000000000000000000000000000000000000000000000 45
 46 0x3400360000000000000000000000000000000000000000000000000000000000 46
 47 0x34003700000000000000000000000000000000000000000000000000000000 47
 44 0x34003400000000000000000000000000000000000000000000000000000000 44
 44 0x34003400000000000000000000000000000000000000000000000000000000 44
 47 0x34003700000000000000000000000000000000000000000000000000000000 47
 43 0x3400330000000000000000000000000000000000000000000000000000000000 43
 50 0x3500300000000000000000000000000000000000000000000000000000000000 50
 44 0x34003400000000000000000000000000000000000000000000000000000000 44

And when I change in the first query 'select * from' to 'select top 18 * from', I also do not get an error!

I don’t know what the problem is and how to do it! Could you help me?

Once again: what I'm trying to do here is get these McTable strings with BigMacs greater than 5.

UPDATE

Steps to reproduce this error:

I prepared the queries, so you can easily get this error in your database:

Create a database TestDB, create a table with

 USE [TestDB] GO /****** Object: Table [dbo].[TestTable] Script Date: 04/08/2009 16:27:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [MyVal] [nvarchar](255) COLLATE Polish_CI_AS NOT NULL, CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 

enter values ​​using:

 delete from TestDB.dbo.TestTable insert into TestDB.dbo.TestTable (MyVal) values ('fd') insert into TestDB.dbo.TestTable (MyVal) values ('54543534') insert into TestDB.dbo.TestTable (MyVal) values ('fat3tv3gv5') insert into TestDB.dbo.TestTable (MyVal) values ('fdf4v43 4v434v') insert into TestDB.dbo.TestTable (MyVal) values (' g dfg dfg df') insert into TestDB.dbo.TestTable (MyVal) values ('f sd 4t4gsdf') insert into TestDB.dbo.TestTable (MyVal) values ('f df 4 trwefg') insert into TestDB.dbo.TestTable (MyVal) values ('f sd f4 fgsfg sd') insert into TestDB.dbo.TestTable (MyVal) values ('54534534') insert into TestDB.dbo.TestTable (MyVal) values ('454') 

This request:

  select CAST(MyVal as int) MyValInt from dbo.TestTable where IsNumeric(MyVal) = 1 

results in valid numbers as shown below:

54543534

54534534

454

And when you try to get the filtered values ​​with this query:

 select * from ( select CAST(MyVal as int) MyValInt from dbo.TestTable where IsNumeric(MyVal) = 1 ) tabela where tabela.MyValInt > 6 

You should get this error, which should not occur:

Msg 245, Level 16, State 1, Line 1 Conversion error when converting nvarchar 'fd' value to int data type.

Answer 1

New answer for your revised scripts. It happens that the SQL Server query optimizer optimizes your subquery. It performs one scan of the test pattern and combines the internal and external WHERE clauses into one. That is why you are still getting the error. To see this, look at the estimated query execution plan and hover over the cluster index scan icon to see what is actually running. You will see that the following predicate is applied during scanning:

 CONVERT(int,[testdb].[dbo].[TestTable].[MyVal],0)>(6) AND isnumeric(CONVERT_IMPLICIT(varchar(510), [testdb].[dbo].[TestTable].[MyVal],0))=(1) 

Therefore, regardless of the structure of your query, it tries to do CAST / CONVERT in each row of the table ...

To avoid this, use a table variable or a temporary table that cannot be optimized:

 DECLARE @integers table ( MyValInt int ) INSERT INTO @integers SELECT CAST(MyVal AS int) FROM dbo.TestTable WHERE ISNUMERIC(MyVal) = 1 SELECT * FROM @integers WHERE MyValInt > 6 

The results that you really want to return will be different, so I suggest storing the primary key along with the int value in a table variable, and then making your final query as a join as follows:

 DECLARE @integers table ( ID int, MyValInt int ) INSERT INTO @integers SELECT ID, CAST(MyVal AS int) FROM dbo.TestTable WHERE ISNUMERIC(MyVal) = 1 SELECT b.* FROM @integers t INNER JOIN TestTable b ON b.ID = t.ID WHERE t.MyValInt > 6 

Answer 2

State 1, line 67 Conversion error when converting nvarchar '***' value to int data type.

You get this value because some of the values ​​in BigMacs.BigMac contain a non-numeric value. In your case, "***".

And when I change in the first query 'select * from' to 'select top 18 * from', I also do not get an error!

This is because at least the first 18 rows returned have BigMacs.BigMac numeric values.

Create a new User-Defined method called isReallyNumeric () that examines what is really numeric or not.

Filter only the numeric value of BigMac using isReallyNumeric () function
I also optimized the query to pour BigMacs.BigMac into an integer once using CTE (Common Table Expression).

 with NumericBigMacs as ( select BigMacs as BigMacsS, CAST(BigMacs as Binary) as BigMacsB, CAST(BigMacs as int) as BigMacsL from McTable where -- Filter only numeric values to compare. -- BigMacs Like '%[0-9]%' dbo.isReallyNumeric(BigMacs) = 1 ) select * from NumericBigMacs NBM where BigMacsL > 5 

Answer 3

The problem is that you can use the CAST value for int if it really contains int. Obviously your first 18 lines. But then, if you include more lines, it reaches the line where the value cannot be passed to int, and you get the error you described. How about this:

 select BigMacs BigMacsS, CAST(BigMacs as Binary) BigMacsB from McTable where BigMacs Like '%[6-9]%' or BigMacs LIKE '%[1-9][0-5]%' 

This will find all lines containing a number in the text of this column that is greater than 5 (provided that it does not have decimal or negative numbers).

Answer 4

OK isnumeric does not always work when storing character data and numbers in one column. It is also not limited to elements that can be converted to integers. See this link for an explanation: http://www.tek-tips.com/faqs.cfm?fid=6423

My first question is: why do you store things that you want to use as numbers and characters in one column? This is a serious design flaw and should be corrected if possible.

I believe that the link can help you understand what to do if you cannot change the structure.

Answer 5

This seems to give the expected results.

change

and includes your where clause

 DECLARE @McTable TABLE (BigMacs VARCHAR(20)) INSERT INTO @McTable VALUES ('1') INSERT INTO @McTable VALUES ('1dqsf') INSERT INTO @McTable VALUES ('qsfsq1') INSERT INTO @McTable VALUES ('10') select BigMacs, cast(BigMacs as Binary) as BigMacsB, cast(BigMacs as int) as BigMacsL from @McTable where IsNumeric(BigMacs) = 1 and cast(BigMacs as int) > 5 

Answer 6

Here's what happens: the BigMacs Like '%[0-9]%' predicate BigMacs Like '%[0-9]%' does not quite do what you think. He selects lines that have at least one digit somewhere in the line.

This is not what you want. You need strings that have only numbers. Unfortunately, LIKE wilcards do not give us an easy way to request this.

We can get close enough for your problem. If we ask

 not (BigMacs like "%[ A-Za-z!@ #$%^&*()=;:'""]%") 

we will filter out most lines that have nothing but numbers. "Most" because our similar pattern does not contain all possible non-numeric characters. This, in turn, should allow the castes to work.

So:

 select * from ( select BigMacs BigMacsS, CAST(BigMacs as Binary) BigMacsB, CAST(BigMacs as int) BigMacsL from McTable where not (BigMacs like "%[ A-Za-z!@ #$%^&*()=;:'""]%") ) table where Cast(table.BigMacsL as int) > 5 

Answer 7

I think using the ISNUMERIC () function could also help.

Example:

 SELECT * FROM ( SELECT CAST(CASE WHEN ISNUMERIC(myval)=1 THEN myval ELSE 0 END AS INT) AS mi FROM dbo.TestTable ) AS t2 WHERE mi>5 

Answer 8

I think David M. nailed him on the head, but for those who asked for a script that reproduces the problem:

 CREATE TABLE dbo.Test_Int_Conversion ( my_id INT IDENTITY NOT NULL, my_str VARCHAR(20) NOT NULL, CONSTRAINT PK_Test_Int_Conversion PRIMARY KEY CLUSTERED (my_id) ) GO INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('1') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('2') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('3') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('4') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('5') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('6') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('7') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('8') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('9') INSERT INTO dbo.Test_Int_Conversion (my_str) VALUES ('*') GO SELECT * FROM ( SELECT my_id, CAST(my_str AS INT) my_strI FROM dbo.Test_Int_Conversion WHERE my_str LIKE '%[0-9]%' ) SQ WHERE CAST(SQ.my_strI AS INT) > 5 

Answer 9

This code should work:

 select tabela.* from ( select CAST(MyVal as int) MyValInt from dbo.TestTable where IsNumeric(MyVal) = 1 ) tabela left join (select 1 a )a on tabela.MyValInt > 6 

I think the reason for the failure of the original query may be related to the order in which the query is evaluated by SQL.