代码之家 › 专栏 › 技术社区 › MAK

为给定值准备WHERE子句字符串

sql-server-2008-r2 tsql sql-server

MAK · 技术社区 · 7 年前

我有以下详细信息准备字符串,如下面的预期结果所示。

鉴于:

DECLARE @VValue VARCHAR(MAX) = 'John Dee|Mak Don'
DECLARE @VColumns VARCHAR(MAX) = 'FName|LName'
DECLARE @VCondition VARCHAR(500) = 'OR'
DECLARE @VPattern VARCHAR(MAX) = 'Start|Exact'
DECLARE @VCheck VARCHAR(MAX) = '1|0'
DECLARE @String VARCHAR(MAX) = ''

预期结果:

(
    ISNULL(PATINDEX(''John%'', FName), ''0'') > 0 AND 
    ISNULL(PATINDEX(''Dee%'', FName), ''0'') > 0
)  
OR 
(
    ISNULL(PATINDEX(''Mak'', LName), ''0'') +  
    ISNULL(PATINDEX(''Don'', LName), ''0'') > 0 
)

我的尝试:

SET @VCondition = '|'+@VCondition;

 SET @String = REPLACE('('+REPLACE(STUFF((SELECT N')  '+DS3.Item+' (' + NCHAR(10) + 
             STUFF((SELECT 
                         CASE WHEN DS4.Item = 'Start' AND DS5.Item = 0
                               THEN
                                    N' + ' +NCHAR(10)+
                                    N'ISNULL(PATINDEX('''''+DSn.Item +'%'''''+ N',' + DSc.Item  + N'),''''0'''')'
                                WHEN DS4.Item = 'Start' AND DS5.Item = 1
                                THEN
                                    N' > 0 AND ' +NCHAR(10)+
                                    N'ISNULL(PATINDEX('''''+DSn.Item +'%'''''+ N',' + DSc.Item  + N'),''''0'''')'
                          ELSE
                                ''
                          END 
                          +
                          CASE WHEN DS4.Item = 'Exact' AND DS5.Item = 0
                               THEN
                                   N' + ' +NCHAR(10)+
                                   N'ISNULL(PATINDEX('''''+DSn.Item +''''''+ N',' + DSc.Item  + N'),''''0'''')'
                               WHEN DS4.Item = 'Exact' AND DS5.Item = 1
                               THEN
                                   N' > 0 AND ' +NCHAR(10)+
                                   N'ISNULL(PATINDEX('''''+DSn.Item +''''''+ N',' + DSc.Item  + N'),''''0'''')'
                          ELSE '' 
                          END
                    FROM dbo.f_split(DS1.Item,' ') DSn
                         CROSS APPLY dbo.f_split(DS2.Item,',') DSc
                    ORDER BY DSc.id, DSn.id
                    FOR XML PATH(N'')),1,4,N'')
            FROM dbo.udf_split (@VValue,'|') DS1
            CROSS APPLY dbo.udf_split (@VColumns,'|') DS2
            CROSS APPLY dbo.udf_split (@VCondition,'|') DS3
            CROSS APPLY dbo.udf_split (@VPattern,'|') DS4
            CROSS APPLY dbo.udf_split (@VCheck, '|') DS5
            WHERE DS1.id = DS2.id AND DS2.ID = DS3.ID AND DS3.ID = DS4.ID AND DS4.ID = DS5.ID
            ORDER BY DS1.id
            FOR XML PATH(N'')),1,9,N''),'&gt;','>') + N') )','&amp;gt;','>');

PRINT(@String);

但我得到了以下结果:

( AND 
    ISNULL(PATINDEX(''John%'', FName), ''0'') > 0 AND 
    ISNULL(PATINDEX(''Dee%'', FName), ''0''))  OR (
    ISNULL(PATINDEX(''Mak'', LName), ''0'') + 
    ISNULL(PATINDEX(''Don'', LName), ''0'')) )

1 回复 | 直到 7 年前

MAK 7 年前

经过多次尝试,下面的工作完成了。

查询:

DECLARE @VValue VARCHAR(MAX) = 'John Dee|Mak Don'
DECLARE @VColumns VARCHAR(MAX) = 'FName|LName'
DECLARE @VCondition VARCHAR(500) = 'OR'
DECLARE @VPattern VARCHAR(MAX) = 'Start|Exact'
DECLARE @VCheck VARCHAR(MAX) = '1|0'
DECLARE @String VARCHAR(MAX) = ''

SET @VCondition = '|'+@VCondition;

 SET @String = REPLACE('('+STUFF((SELECT N' > 0 )  '+DS3.Item+' (' + NCHAR(10) + 
             REPLACE(STUFF((SELECT 
                         CASE WHEN DS4.Item = 'Start' AND DS5.Item = 0
                               THEN
                                    N'    +     ' +NCHAR(10)+
                                    N'ISNULL(PATINDEX('''''+DSn.Item +'%'''''+ N',' + DSc.Item  + N'),''''0'''')'
                                WHEN DS4.Item = 'Start' AND DS5.Item = 1
                                THEN
                                    N' > 0 AND ' +NCHAR(10)+
                                    N'ISNULL(PATINDEX('''''+DSn.Item +'%'''''+ N',' + DSc.Item  + N'),''''0'''')'
                          ELSE
                                ''
                          END 
                          +
                          CASE WHEN DS4.Item = 'Exact' AND DS5.Item = 0
                               THEN
                                   N'    +     ' +NCHAR(10)+
                                   N'ISNULL(PATINDEX('''''+DSn.Item +''''''+ N',' + DSc.Item  + N'),''''0'''')'
                               WHEN DS4.Item = 'Exact' AND DS5.Item = 1
                               THEN
                                   N' > 0 AND ' +NCHAR(10)+
                                   N'ISNULL(PATINDEX('''''+DSn.Item +''''''+ N',' + DSc.Item  + N'),''''0'''')'
                          ELSE '' 
                          END
                    FROM dbo.f_split(DS1.Item,' ') DSn
                         CROSS APPLY dbo.f_split(DS2.Item,',') DSc
                    ORDER BY DSc.id, DSn.id
                    FOR XML PATH(N'')),1,11,N''),'&gt;','>')
            FROM dbo.udf_split (@VValue,'|') DS1
            CROSS APPLY dbo.udf_split (@VColumns,'|') DS2
            CROSS APPLY dbo.udf_split (@VCondition,'|') DS3
            CROSS APPLY dbo.udf_split (@VPattern,'|') DS4
            CROSS APPLY dbo.udf_split (@VCheck, '|') DS5
            WHERE DS1.id = DS2.id AND DS2.ID = DS3.ID AND DS3.ID = DS4.ID AND DS4.ID = DS5.ID
            ORDER BY DS1.id
            FOR XML PATH(N'')),1,13,N''),'&gt;','>') + N' > 0 ) ';

PRINT(@String);

输出:

( 
    ISNULL(PATINDEX(''John%'',FName),''0'') > 0 AND 
    ISNULL(PATINDEX(''Dee%'',FName),''0'') > 0 
)  
OR 
(
    ISNULL(PATINDEX(''Mak'',LName),''0'')    +     
    ISNULL(PATINDEX(''Don'',LName),''0'') > 0 
)