sql - 在 SQL 查询中允许 *

Question

我有一个存储过程，我想在其中允许 * 以便如果用户:

输入 t* 然后更高，茶返回

类型 * 或 .然后返回所有结果

输入 t 然后返回所有具有 t 的结果，如 ptv、tall、sit

我的存储过程是这样的:

ALTER PROCEDURE [dbo].[SearchEntityDataNew]
    @SearchText varchar(100) = '*'

AS
BEGIN
    SELECT 'Entity' as pagetype,EntityID,EntityData,EntityDataID 
    FROM EntityData 
    WHERE  EntityData LIKE '%' + @SearchText+ '%'
    OR @SearchText = '*'

    union all

    SELECT 'Property' as pagetype,PropertyID,PropertyValue,EntityDataID  
    FROM EntityDataProperty 
    WHERE  PropertyValue LIKE '%' + @SearchText+ '%'
    OR @SearchText = '*'
END

Answer 1

你能不能不使用:

WHERE  PropertyValue LIKE REPLACE(@SearchText, '*', '%');

以下测试:

-- SAMPLE TABLE
DECLARE @T TABLE (A VARCHAR(100));
INSERT @T VALUES ('tea'), ('test'), ('foo'), ('bar'), ('foobar');

-- TEST USING WILDCARD AT END
DECLARE @SearchText VARCHAR(100) = 't*';
SELECT  *
FROM    @T
WHERE   A LIKE REPLACE(@SearchText, '*', '%');

-- TEST USING ONLY WILD CARD
SET @SearchText = '*';
SELECT  *
FROM    @T
WHERE   A LIKE REPLACE(@SearchText, '*', '%');

--TEST USING WILD CARD AT START
SET @SearchText = '*bar';
SELECT  *
FROM    @T
WHERE   A LIKE REPLACE(@SearchText, '*', '%');

--TEST USING WILD CARD AT START AND END
SET @SearchText = '*a*';
SELECT  *
FROM    @T
WHERE   A LIKE REPLACE(@SearchText, '*', '%');

输出:

-- Wild card at end: "t*"
tea
test

-- Wild card only: "*"
tea
test
foo
bar
foobar

-- Wild card at start: "*bar"
bar
foobar

-- wild card at start and end: "*a*"
tea
bar
foobar

编辑

我刚刚重新阅读了您的问题，并看到了当不存在通配符时在开头和结尾都假定通配符的要求，在这种情况下，您可以将其添加到程序的开头:

IF CHARINDEX('*', @SearchText) = 0
    SET @SearchText = '%' + @SearchText + '%'
ELSE
    SET @SearchText = REPLACE(@SearchText, '*', '%')

然后简单地使用

WHERE  PropertyValue LIKE @SearchText