CODEWITHSUNDEEP
sqlsql-server-2005t-sqlquery-optimization
Tom Smykowski
Tom Smykowski

Reputation: 26089

How to filter nvarchar greater than numeric value?

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

What I do is:

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 in result I get an error:

State 1, Line 67 Conversion failed when converting the nvarchar value '***' to data type int.

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

6    0x360000000000000000000000000000000000000000000000000000000000 6
23   0x320033000000000000000000000000000000000000000000000000000000 23
22   0x320032000000000000000000000000000000000000000000000000000000 22
24   0x320034000000000000000000000000000000000000000000000000000000 24
25   0x320035000000000000000000000000000000000000000000000000000000 25
3    0x330000000000000000000000000000000000000000000000000000000000 3
17   0x310037000000000000000000000000000000000000000000000000000000 17
17   0x310037000000000000000000000000000000000000000000000000000000 17
19   0x310039000000000000000000000000000000000000000000000000000000 19
20   0x320030000000000000000000000000000000000000000000000000000000 20
659  0x360035003900000000000000000000000000000000000000000000000000 659
1    0x310000000000000000000000000000000000000000000000000000000000 1
43   0x340033000000000000000000000000000000000000000000000000000000 43
44   0x340034000000000000000000000000000000000000000000000000000000 44
45   0x340035000000000000000000000000000000000000000000000000000000 45
46   0x340036000000000000000000000000000000000000000000000000000000 46
47   0x340037000000000000000000000000000000000000000000000000000000 47
44   0x340034000000000000000000000000000000000000000000000000000000 44
44   0x340034000000000000000000000000000000000000000000000000000000 44
47   0x340037000000000000000000000000000000000000000000000000000000 47
43   0x340033000000000000000000000000000000000000000000000000000000 43
50   0x350030000000000000000000000000000000000000000000000000000000 50
44   0x340034000000000000000000000000000000000000000000000000000000 44

And when I change in first query 'select * from' to 'select top 18 * from' than I do not get error too!

I don't know what is the problem and how to make it work! Could you please help me?

Once again: what I try to accomplish here is to get these McTable rows that have BigMacs value greater than 5.

UPDATE

Steps to reproduce this error:

I've prepared queries so You can easily get this error on your database:

Create database TestDB, create 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]

insert values with:

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 query:

    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 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 that should not occur:

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

Upvotes: 5

Views: 9483

Answers (9)

kristof
kristof

Reputation: 53834

That 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 that the reason the original query fails may be related to the order in which the query is evaluated by SQL.

Upvotes: 0

Alexander Prokofyev
Alexander Prokofyev

Reputation: 34515

I think using of ISNUMERIC() function also could 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

Upvotes: 0

David M
David M

Reputation: 72870

New answer for your revised scripts. What is happening is that the SQL Server query optimiser is optimising out your subquery. It is performing a single scan of the test table, and combining the inner and outer WHERE clauses into one. That's why you get the error still. To see this, view the estimated execution plan for the query, and hover over the Clustered Index Scan icon to see what is actually being carried out. You will see the following predicate being applied on the scan:

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

So regardless of the structure of your query, it is trying to do the CAST/CONVERT on every row in the table...

To avoid this, use a table variable or temporary table that can't be optimised out:

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 set you actually want to return will be different, so I'd suggest storing the primary key along with the int value in the table variable, and then doing your final query as a join like this:

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

Upvotes: 7

Tom H
Tom H

Reputation: 47464

I think that David M. has nailed it 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

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58451

This seems to give the expected results

edit

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

Upvotes: 0

dance2die
dance2die

Reputation: 36915

State 1, Line 67 Conversion failed when converting the nvarchar value '***' to data type int.

You are getting this value since some of the values in BigMacs.BigMac contains a non-numeric value. In your case "***".

And when I change in first query 'select * from' to 'select top 18 * from' than I do not get error too!

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

Create a new User-Defined method called isReallyNumeric(), which addresses what is really numeric or not.

Filter out only numeric BigMac using isReallyNumeric() function
I have also optimized the query to cast BigMacs.BigMac into 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

Upvotes: 2

HLGEM
HLGEM

Reputation: 96570

OK isnumeric doesn't always work when storing character data and numbers in the same column. Nor is it limited to items which 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 are you storing things things you want to use as numbers and characters in the same column? This is a severe design flaw and should be corrected if at all possible.

I believe the link might help you figure out what do do if you cannot change the structure.

Upvotes: 1

David M
David M

Reputation: 72870

The problem is that you can only CAST a value to an int if it does indeed contain an int. Clearly your first 18 rows do. But then if you include more rows, it reaches a row where the value cannot be cast to an int and you get the error you describe. 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]%'

That will find all rows containing a number within the text of this column that is greater than 5 (assuming there are no decimals or negative numbers contained).

Upvotes: 1

tpdi
tpdi

Reputation: 35151

Here's what's happening: the predicate BigMacs Like '%[0-9]%' doesn't quite do what you think. It selects rows that have at least one digit somewhere in the string.

This is not what you want. You want strings that only have digits. Unfortunately, the LIKE wilcards don't give us an easy way to ask for that.

We may come close enough for your problem. If we ask for 

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

we'll filter out most rows that have anything but numbers. "Most", because our like wildcard doesn't contain all possible non-numeric characters. This, in turn, should allow the cast 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

Upvotes: 0

Related Questions