已知原串:
@DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;' --注意其中的:;和,数据库中CatePath字段的记录如下(注意,所有的字串都是标准的结构):
1 ;,0,2,;
2 ;,0,5,;
3 ;,0,7,;
4 ;,0,11,;
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;
9 ;,0,2,12,17,;
10 ;,0,7,19,;
11 ;,0,11,20,;
12 ;,0,11,20,28;如何匹配,即:@CatePath = ';,0,2,;'中后面多一位的,如:,;,0,2,13,;或;,0,2,15,;或;,0,2,17,;等。
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;
而不匹配:
1 ;,0,2,;
2 ;,0,5,;
3 ;,0,7,;
4 ;,0,11,;
9 ;,0,2,12,17,;
10 ;,0,7,19,;
11 ;,0,11,20,;
12 ;,0,11,20,28;@DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;'SELECT *
FROM TEST
WHERE CateShare LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) + ';' + '%'请帮我写完整,并符合上述要求的SQL语句。
@DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;' --注意其中的:;和,数据库中CatePath字段的记录如下(注意,所有的字串都是标准的结构):
1 ;,0,2,;
2 ;,0,5,;
3 ;,0,7,;
4 ;,0,11,;
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;
9 ;,0,2,12,17,;
10 ;,0,7,19,;
11 ;,0,11,20,;
12 ;,0,11,20,28;如何匹配,即:@CatePath = ';,0,2,;'中后面多一位的,如:,;,0,2,13,;或;,0,2,15,;或;,0,2,17,;等。
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;
而不匹配:
1 ;,0,2,;
2 ;,0,5,;
3 ;,0,7,;
4 ;,0,11,;
9 ;,0,2,12,17,;
10 ;,0,7,19,;
11 ;,0,11,20,;
12 ;,0,11,20,28;@DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;'SELECT *
FROM TEST
WHERE CateShare LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) + ';' + '%'请帮我写完整,并符合上述要求的SQL语句。
DECLARE @CatePath VARCHAR(4000)
SET @CatePath = ';,0,2,;' --注意其中的:;和,
select * from tb where charindex(replace(@catePath,';',''),c)>0 and (len(c)-len(replace(c,',',''))=4 or len(c)-len(replace(c,';',''))>2)
id c
----------- -------------------------------------------------------------------------5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;(4 行受影响)
-- Author :SQL77(只为思齐老)
-- Date :2010-05-03 13:56:57
-- 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] int,[b] varchar(27))
insert #tb
select 1,';,0,2,;' union all
select 2,';,0,5,;' union all
select 3,';,0,7,;' union all
select 4,';,0,11,;' union all
select 5,';,0,2,11,;' union all
select 6,';,0,1,12,;,0,2,13,;' union all
select 7,';,0,11,20,;,0,2,15,;,0,11,;' union all
select 8,';,0,2,17,;,0,5,170,;' union all
select 9,';,0,2,12,17,;' union all
select 10,';,0,7,19,;' union all
select 11,';,0,11,20,;' union all
select 12,';,0,11,20,28;'
--------------开始查询--------------------------
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;'SELECT *
FROM #tb
WHERE b LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) + '%'----------------结果----------------------------
/* (12 行受影响)
a b
----------- ---------------------------
1 ;,0,2,;
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;
9 ;,0,2,12,17,;(6 行受影响)
*/楼主可以变通一下
-- Author :SQL77(只为思齐老)
-- Date :2010-05-03 13:56:57
-- 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] int,[b] varchar(27))
insert #tb
select 1,';,0,2,;' union all
select 2,';,0,5,;' union all
select 3,';,0,7,;' union all
select 4,';,0,11,;' union all
select 5,';,0,2,11,;' union all
select 6,';,0,1,12,;,0,2,13,;' union all
select 7,';,0,11,20,;,0,2,15,;,0,11,;' union all
select 8,';,0,2,17,;,0,5,170,;' union all
select 9,';,0,2,12,17,;' union all
select 10,';,0,7,19,;' union all
select 11,';,0,11,20,;' union all
select 12,';,0,11,20,28;'
--------------开始查询--------------------------
--DECLARE @CatePath NVARCHAR(4000)
--SET @CatePath = ';,0,2,;'
--SELECT
--parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),1),
--parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),2),
--parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),3)
--FROM #tb where len(b)-len(replace(b,';',''))>2DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;'select * from #tb
WHERE
(b LIKE '%' + LEFT(@CatePath, LEN(@CatePath)-1) + '%'
and len(b)-len(replace(b,',',''))=len(@CatePath)-len(replace(@CatePath,',',''))+1
and len(b)-len(replace(b,';',''))=2
)
or
(
len(b)-len(replace(b,';',''))>2 and charindex(replace(@CatePath,';',''),b)>0 and (len(parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),1))-
len(replace(parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),1),',',''))
=len(@CatePath)-len(replace(@CatePath,',',''))+1 )
or
(
len(parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),2))-
len(replace(parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),2),',',''))
=len(@CatePath)-len(replace(@CatePath,',',''))+1 )
or
(
len(parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),3))-
len(replace(parsename(replace(right(left(b,len(b)-1),len(left(b,len(b)-1))-1),';','.'),3),',',''))
=len(@CatePath)-len(replace(@CatePath,',',''))+1 )
)----------------结果----------------------------
/*(12 行受影响)
a b
----------- ---------------------------
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;(4 行受影响)
*/楼主这样的表结构会把你弄晕
-- Author : htl258(Tony)
-- Date : 2010-05-03 14:12:04
-- 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] ([id] [int],[col] [nvarchar](30))
INSERT INTO [tb]
SELECT '1',';,0,2,;' UNION ALL
SELECT '2',';,0,5,;' UNION ALL
SELECT '3',';,0,7,;' UNION ALL
SELECT '4',';,0,11,;' UNION ALL
SELECT '5',';,0,2,11,;' UNION ALL
SELECT '6',';,0,1,12,;,0,2,13,;' UNION ALL
SELECT '7',';,0,11,20,;,0,2,15,;,0,11,;' UNION ALL
SELECT '8',';,0,2,17,;,0,5,170,;' UNION ALL
SELECT '9',';,0,2,12,17,;' UNION ALL
SELECT '10',';,0,7,19,;' UNION ALL
SELECT '11',';,0,11,20,;' UNION ALL
SELECT '12',';,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,2,;' --注意其中的:;和,SELECT * FROM [tb] WHERE dbo.fn_test(col,@CatePath)=1
/*
id col
----------- ------------------------------
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;(4 行受影响)
*/
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;' --注意其中的:;和,
--数据库中CatePath字段的记录如下:
select * from (
select 1 as id,';,0,2,;' as CateShare union all
select 2,';,0,5,;' union all
select 3,';,0,7,;' union all
select 4,';,0,11,;' union all
select 5,';,0,2,11,;' union all
select 6,';,0,1,12,;,0,2,13,;' union all
select 7,';,0,11,20,;,0,2,15,;,0,11,;' union all
select 8,';,0,2,17,;,0,5,170,;' union all
select 9,';,0,2,12,17,;' union all
select 10,';,0,7,19,;' union all
select 11,';,0,11,20,;' union all
select 12,';,0,11,20,28;') TEST
WHERE charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)>0
and
charindex(',;',CateShare,(charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1))>0
and
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)) - (charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1)
)
)=0
--解法1.2,
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;' --注意其中的:;和,
--数据库中CatePath字段的记录如下:
select * from (
select 1 as id,';,0,2,;' as CateShare union all
select 2,';,0,5,;' union all
select 3,';,0,7,;' union all
select 4,';,0,11,;' union all
select 5,';,0,2,11,;' union all
select 6,';,0,1,12,;,0,2,13,;' union all
select 7,';,0,11,20,;,0,2,15,;,0,11,;' union all
select 8,';,0,2,17,;,0,5,170,;' union all
select 9,';,0,2,12,17,;' union all
select 10,';,0,7,19,;' union all
select 11,';,0,11,20,;' union all
select 12,';,0,11,20,28;') TEST
WHERE charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)>0
and
charindex(',;',CateShare,(charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1))>0
and
substring(CateShare,charindex( ',',CateShare,charindex(LEFT(@CatePath,LEN(@CatePath)-1),CateShare)+LEN(@CatePath)-1)+1,1)=';'
--解法2,比较确定数字位数的情况
DECLARE @CatePath NVARCHAR(4000)
SET @CatePath = ';,0,2,;' --注意其中的:;和,
--数据库中CatePath字段的记录如下:
select * from (
select 1 as id,';,0,2,;' as CateShare union all
select 2,';,0,5,;' union all
select 3,';,0,7,;' union all
select 4,';,0,11,;' union all
select 5,';,0,2,11,;' union all
select 6,';,0,1,12,;,0,2,13,;' union all
select 7,';,0,11,20,;,0,2,15,;,0,11,;' union all
select 8,';,0,2,17,;,0,5,170,;' union all
select 9,';,0,2,12,17,;' union all
select 10,';,0,7,19,;' union all
select 11,';,0,11,20,;' union all
select 12,';,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],'+ ';' + '%'/*
id CateShare
----------- ---------------------------
5 ;,0,2,11,;
6 ;,0,1,12,;,0,2,13,;
7 ;,0,11,20,;,0,2,15,;,0,11,;
8 ;,0,2,17,;,0,5,170,;(所影响的行数为 4 行)*/