如何匹配：;,0,;

[wtoeb]

已知原串：
@DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'
数据库中CatePath字段的记录如下：
;,0,2,;
;,0,5,;
;,0,7,;
;,0,11,;
;,0,1,12,;
;,0,2,13,;
;,0,2,15,;
;,0,5,17,;
;,0,7,19,;
;,0,11,20,;
;,0,11,20,28;
如何匹配：
;,0,2,;
;,0,5,;
;,0,7,;
;,0,11,;
而不匹配：
;,0,1,12,;
;,0,2,13,;
;,0,2,15,;
;,0,5,17,;
;,0,7,19,;
;,0,11,20,;
;,0,11,20,28;

要求在SQL语句中实现：
示例如下：

@DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'

SELECT *
FROM TEST
WHERE CateShare LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) + ';' + '%'

请帮我写完整，并符合上述要求的SQL语句。

[SQL77]

为啥不匹配其它的,什么规则

[bancxc]

数都好的个数就可以了么

[wtoeb]

就是匹配：';,0,;'
在其后有一位的串，如：';,0,2,;'，而不匹配有n位的串，如：';,0,2,13,;'。

[jimenzhou]

这里面有些什么规律呢,我没看得出来,提示一下^

[SQL77]

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-05-02 18:47:29
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
--	Oct 14 2005 00:33:37 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb([a] varchar(13))
insert #tb
select ';,0,2,;' union all
select ';,0,5,;' union all
select ';,0,7,;' union all
select ';,0,11,;' union all
select ';,0,1,12,;' union all
select ';,0,2,13,;' union all
select ';,0,2,15,;' union all
select ';,0,5,17,;' union all
select ';,0,7,19,;' union all
select ';,0,11,20,;' union all
select ';,0,11,20,28;'
--------------开始查询--------------------------
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'

SELECT *
FROM #tb
WHERE a LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) + '%'
and len(a)-len(replace(a,',',''))=len(@CatePath)-len(replace(@CatePath,',',''))+1
----------------结果----------------------------
/* 

(11 行受影响)
a
-------------
;,0,2,;
;,0,5,;
;,0,7,;
;,0,11,;

(4 行受影响)


*/

[gw6328]

[Quote=引用 5 楼 sql77 的回复:]
SQL code
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-05-02 18:47:29
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (In……
[/Quote]
学习。
:SQL77(只为思齐老)
呵呵

[feixianxxx]

--如果数据都比较规范

select * from #tb
where  len(a)-len(REPLACE(a,',',''))=len(@CatePath)-len(REPLACE(@CatePath,',',''))+1

[xman_78tom]

DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'

select * from tb 
where [a] like LEFT(@CatePath, LEN(@CatePath)-1)+'[0-9],;'
or [a] like LEFT(@CatePath, LEN(@CatePath)-1)+'[0-9][0-9],;';

[永生天地]

--解法1.1，
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'
--数据库中CatePath字段的记录如下：
select *  from (
select ';,0,2,;' as CateShare  union all
select ';,0,5,;' union all
select ';,0,7,;' union all
select ';,0,11,;' union all
select ';,0,1,12,;' union all
select ';,0,2,13,;' union all
select ';,0,2,15,;' union all
select ';,0,5,17,;' union all
select ';,0,7,19,;' union all
select ';,0,11,20,;' union all
select ';,0,11,20,28;') TEST
WHERE charindex(',',
	substring(
		CateShare,
		charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1,
		charindex(',;',CateShare) - (charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1)
	) 
)=0


--解法1.2，
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'
--数据库中CatePath字段的记录如下：
select *  from (
select ';,0,2,;' as CateShare  union all
select ';,0,5,;' union all
select ';,0,7,;' union all
select ';,0,11,;' union all
select ';,0,1,12,;' union all
select ';,0,2,13,;' union all
select ';,0,2,15,;' union all
select ';,0,5,17,;' union all
select ';,0,7,19,;' union all
select ';,0,11,20,;' union all
select ';,0,11,20,28;') TEST
where substring(CateShare,charindex( ',',CateShare,charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1)+1,1)=';'


--解法2，比较确定数字位数的情况
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'
--数据库中CatePath字段的记录如下：
select * from (
select ';,0,2,;' as CateShare  union all
select ';,0,5,;' union all
select ';,0,7,;' union all
select ';,0,11,;' union all
select ';,0,1,12,;' union all
select ';,0,2,13,;' union all
select ';,0,2,15,;' union all
select ';,0,5,17,;' union all
select ';,0,7,19,;' union all
select ';,0,11,20,;' union all
select ';,0,11,20,28;') TEST
WHERE CateShare LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) +'[0-9],'+ ';' + '%'
or CateShare LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) +'[0-9][0-9],'+ ';' + '%'

/*
CateShare     
------------- 
;,0,2,;
;,0,5,;
;,0,7,;
;,0,11,;

（所影响的行数为 4 行）*/

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-03 15:03:43
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([col] [nvarchar](20))
INSERT INTO [tb]
SELECT ';,0,2,;' UNION ALL
SELECT ';,0,5,;' UNION ALL
SELECT ';,0,7,;' UNION ALL
SELECT ';,0,11,;' UNION ALL
SELECT ';,0,1,12,;' UNION ALL
SELECT ';,0,2,13,;' UNION ALL
SELECT ';,0,2,15,;' UNION ALL
SELECT ';,0,5,17,;' UNION ALL
SELECT ';,0,7,19,;' UNION ALL
SELECT ';,0,11,20,;' UNION ALL
SELECT ';,0,11,20,28;'

--SELECT * FROM [tb]

-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GO
CREATE FUNCTION fn_test(@col nvarchar(4000),@CatePath nvarchar(4000))
RETURNS bit
AS
BEGIN
    DECLARE @b bit,@col1 nvarchar(4000)
    SET @b=0
    WHILE PATINDEX(';%;',@col)>0
    BEGIN
        SET @col1=LEFT(@col,CHARINDEX(';',STUFF(@col,1,1,'')))
        IF @col1 LIKE LEFT(@CatePath,LEN(@CatePath)-1)+'%' 
            AND LEN(@CatePath)-LEN(REPLACE(@CatePath,',',''))+1
               =LEN(@col1)-LEN(REPLACE(@col1,',',''))
        BEGIN
            SET @b=1 
            BREAK
        END        
        SET @col=STUFF(@col,1,CHARINDEX(';',STUFF(@col,1,1,'')),'')
    END
    RETURN @b
END
GO
    

DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,;'

SELECT * FROM [tb] WHERE dbo.fn_test(col,@CatePath)=1
/*
col
--------------------
;,0,2,;
;,0,5,;
;,0,7,;
;,0,11,;

(4 行受影响)
*/