TSQL 在带有动态 sql 的 where 子句中使用通配符

发布于 2024-08-09 09:39:36 字数 734 浏览 2 评论 0原文

看起来，在带有通配符的条件和动态 sql 内部的变量中使用 LIKE 不起作用，尽管它不会给出错误。这是一个例子。

名为 code 的列具有 A0B01C02、A0B02C2D05、A0B02C2D05 等值，我试图匹配包含“B1”等子集的行。当我这样做时，它会起作用并按预期返回结果。

set @sql='select * from table where code like ''%B01%'''
exec sp_executesql @sql

如果我对变量的值进行硬编码设置@code='B01' 并修改 sql 语句以连接引号和通配符：

set @sql='select * from table where code like ' +''''+ '%'+@code + '%' + ''''
exec sp_executesql @sql

这会按预期返回结果，但我必须对变量进行硬编码。但是，当我需要使用 B01 的变量进行匹配并且该变量是使用 select 语句设置的时，我没有返回任何结果。我像这样定义了一个 nvarchar：

set @code=(select top 1 code from anotherTable where USERID=@PersonId)

不过，我确认上面的 select 语句返回了预期的代码。没有错误，但查询“执行成功”。我是否遗漏了 where 子句的语法中的某些内容？

原文

It appears that using the LIKE in a condition with wildcards and a variable inside of dynamic sql doesn't work, although it doesn't give an error. Here's an example.

The column called code has values like A0B01C02,A0B02C2D05,A0B02C2D05, etc and I am trying to match on rows containing a subset like 'B1'. When I do this it works and returns results as expected.

set @sql='select * from table where code like ''%B01%'''
exec sp_executesql @sql

If I hardcode the value of the variable
set @code='B01'
and modify the sql statement to concatenate the quotes and wildcards:

set @sql='select * from table where code like ' +''''+ '%'+@code + '%' + ''''
exec sp_executesql @sql

This returns the results as expected, but I had to hard code the variable. However, when I need to make the match using a variable for B01 and that the variable is set with a select statement, I don't get any results returned. I define an nvarchar like this:

set @code=(select top 1 code from anotherTable where USERID=@PersonId)

I confirmed that the select statement above returns the expected code, however. There is no error, but the query is "executed successfully". Am I missing something in the syntax for the where clause?

分享到QQ

分享到微博

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

发布评论

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

笑，眼淚并存 2024-08-16 09:39:36

您可以在 http://ask.sqlservercentral.com/questions/275/dynamic-where-clause-how-can-i-use-a-variable-in-an- in-谓词/312#312
我的答案是执行“按逗号解析”功能。

/*****************************************************************
**** Parse A Comma Delimited String Into A Table
*****************************************************************/
ALTER  FUNCTION [dbo].[ParseByComma] (
    @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
    VarSubString VARCHAR(50)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(50)

    -- Remove All Spaces
    SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END

You can find a discussion of this at http://ask.sqlservercentral.com/questions/275/dynamic-where-clause-how-can-i-use-a-variable-in-an-in-predicate/312#312
My answer was to do the Parse By Comma Function.

/*****************************************************************
**** Parse A Comma Delimited String Into A Table
*****************************************************************/
ALTER  FUNCTION [dbo].[ParseByComma] (
    @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
    VarSubString VARCHAR(50)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(50)

    -- Remove All Spaces
    SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END

回复收藏 0 原文