仅检查字符串中存在的特定字段

我使用CTE拆分函数

CREATE function [dbo].[fn_string_split](@str NVARCHAR(MAX)
, @delimiter NVARCHAR(255))
returns table
as
return
WITH a AS(
        SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@delimiter,@str) idx2
        UNION ALL
        SELECT idx2+1,CHARINDEX(@delimiter,@str,idx2+1)
        FROM a
        WHERE idx2>0
    )
    SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value, row_number() over (Order by idx1) RN
    FROM a

然后对数据进行利用,以确定每个字符串中的条件数量,然后进行筛选。

DECLARE @Condition varchar(max) = 'Number =''12908,13232323,12122'' AND Name=''Mak'''

DECLARE @Condition1 varchar(max) = 'Address=''UK'' AND Number =''12908'' AND Name=''Mak'''

DECLARE @Condition2 varchar(max) = 'Number =''12908,13232323'''

DECLARE @Table AS TABLE (ID INT, Condition varchar(MAX))
INSERT INTO @Table VALUES
(0,@Condition),(1,@Condition1),(2,@Condition2);

WITH CTE AS (
SELECT T.*, C.value,COUNT(1) OVER (PARTITION BY ID) Cnt
FROM @Table T
CROSS APPLY dbo.fn_string_split(Condition,'AND') C 
)
SELECT * FROM cte WHERE Cnt=1 AND LEFT(REPLACE(value,' ',''),7) = 'Number='

可以做一些更多的调整来排除异常情况,但希望这会让你开始。

使用like查找不带“and”的“number”。

DECLARE @Condition varchar(max) = 'Number =''12908,13232323,12122'' AND Name=''Mak'''
DECLARE @Condition1 varchar(max) = 'Address=''UK'' AND Number =''12908'' AND Name=''Mak'''
DECLARE @Condition2 varchar(max) = 'Number =''12908,13232323'''

查询

Select conditions From (Values (@Condition),(@Condition1),(@Condition2)) c(conditions)
Where conditions Like '%Number%' And conditions Not Like '%AND%'

尝试了以下操作并完成了任务。

DECLARE @Condition varchar(max) = 'Number =''12908,13232323,12122'' AND Name=''Mak'''

DECLARE @Condition1 varchar(max) = 'Address=''UK'' OR Number =''12908'' AND Name=''Mak'''

DECLARE @Condition2 varchar(max) = 'Number =''12908,13232323'''

IF REPLACE(REPLACE(@Condition,' OR ',' @ '),' AND ',' @ ') NOT LIKE '%@%' 
BEGIN
    PRINT(@Condition);
END
ELSE IF REPLACE(REPLACE(@Condition1,' OR ',' @ '),' AND ',' @ ') NOT LIKE '%@%' 
BEGIn
    PRINT(@Condition1);
END
ELSE IF REPLACE(REPLACE(@Condition2,' OR ',' @ '),' AND ',' @ ') NOT LIKE '%@%'  
BEGIn
    PRINT(@Condition2);
END
ELSE
BEGIN
    PRINT('none');
END