如何过滤大于数值的nvarchar？

发布于 2024-07-16 20:15:20 字数 4322 浏览 4 评论 0原文

我有一个 MS SQL 表 McTable，其中包含 BigMacs nvarchar(255) 列。我想获取 BigMacs 值大于 5 的行。

我所做的是：

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

结果我收到错误：

状态 1，第 67 行转换失败转换 nvarchar 值时 '***' 到数据类型 int。

但是当我删除最后一个过滤器 where Cast(table.BigMacsL as int) > 5 它有效，我得到了这个结果：

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

当我将第一个查询“select * from”更改为“select top 18 * from”时，我也没有收到错误！

我不知道问题是什么以及如何让它发挥作用！请你帮助我好吗？

再说一次：我在这里尝试完成的是获取 BigMacs 值大于 5 的这些 McTable 行。

更新

重现此错误的步骤：

我已经准备好查询，以便您可以轻松地在数据库上收到此错误：

创建数据库 TestDB，创建表：

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]

插入值：

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')

此查询：

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

产生有效数字，如下所示：

54543534
54534534
454

当您尝试使用此查询获取过滤值时：

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

您应该收到不应发生的错误：

Msg 245，Level 16，State 1，Line 1 将 nvarchar 值“fd”转换为数据类型 int 时转换失败。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

够运 2024-07-23 20:15:21

发生的事情是这样的：谓词 BigMacs Like '%[0-9]%' 并不完全符合您的想法。它选择字符串中至少有一位数字的行。

这不是你想要的。您想要仅包含数字的字符串。不幸的是，LIKE 通配符并没有为我们提供一种简单的方法来要求这一点。

我们可能足够接近您的问题。如果我们要求，

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

我们将过滤掉大多数包含但数字的行。 “大多数”，因为我们的 like 通配符不包含所有可能的非数字字符。反过来，这应该能让演员阵容发挥作用。

所以：

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

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

回复收藏 0 原文

宛菡 2024-07-23 20:15:21

这似乎给出了预期的结果

编辑

并且包括您的where子句

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

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

回复收藏 0 原文

能怎样 2024-07-23 20:15:21

我认为 David M. 已经把它钉在了头上，但对于那些要求重现该问题的脚本的人来说：

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

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

回复收藏 0 原文

吖咩 2024-07-23 20:15:21

我认为使用 ISNUMERIC() 函数也有帮助。

例子：

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

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

回复收藏 0 原文

金橙橙 2024-07-23 20:15:21

该代码应该可以工作：

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

我认为原始查询失败的原因可能与 SQL 评估查询的顺序有关。

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.

回复收藏 0 原文

笑脸一如从前 2024-07-23 20:15:20

您修改后的脚本的新答案。发生的情况是 SQL Server 查询优化器正在优化您的子查询。它对测试表执行一次扫描，并将内部和外部 WHERE 子句合并为一个。这就是为什么你仍然收到错误的原因。要查看这一点，请查看查询的估计执行计划，并将鼠标悬停在“聚集索引扫描”图标上以查看实际执行的内容。您将看到扫描上应用以下谓词：

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

因此，无论查询的结构如何，它都会尝试对表中的每一行执行 CAST/CONVERT...

要避免这种情况，请使用表变量或临时表无法优化：

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

您实际想要返回的结果集会有所不同，因此我建议将主键与 int 值一起存储在表变量中，然后将最终查询作为这样的联接进行:

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

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

回复收藏 0 原文

心房敞 2024-07-23 20:15:20

状态 1，第 67 行将 nvarchar 值“***”转换为数据类型 int 时转换失败。

您获得此值是因为 BigMacs.BigMac 中的某些值包含非数字值。在你的情况下“***”。

当我将第一个查询“select * from”更改为“select top 18 * from”时，我也没有收到错误！

这是因为首先返回的至少 18 行具有数字BigMacs.BigMac 值。

创建一个名为 isReallyNumeric()，它解决什么是真正的数字或不是数字。

使用 isReallyNumeric() 函数仅过滤掉数字 BigMac
我还优化了查询，使用 CTE（通用表表达式）将 BigMacs.BigMac 转换为整数一次。

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

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

回复收藏 0 原文

陈年往事 2024-07-23 20:15:20

问题是，只有当某个值确实包含 int 时，您才能将它强制转换为 int。显然你的前 18 行就是这样。但是，如果您包含更多行，它会到达无法将值转换为 int 的行，并且您会收到您所描述的错误。怎么样：

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

这将找到该列文本中包含大于 5 的数字的所有行（假设不包含小数或负数）。

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).

回复收藏 0 原文