/*
标题:分解字符串并查询相关数据
作者:范中磊
说明:通过使用函数等方法分解字符串查询相关数据。
问题:通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
6 6,7
*/
-- ---------------------------
create table tb (ID int , TypeID varchar ( 30 ))
insert into tb values ( 1 , ' 1,2,3,4,5,6,7,8,9,10,11,12 ' )
insert into tb values ( 2 , ' 2,3 ' )
insert into tb values ( 3 , ' 3,7,8,9 ' )
insert into tb values ( 4 , ' 2,6 ' )
insert into tb values ( 5 , ' 4,5 ' )
insert into tb values ( 6 , ' 6,7 ' )
go
-- ---------------------------
-- 如果仅仅是一个,如@str = '1'.
declare @str as varchar ( 30 )
set @str = ' 1 '
select * from tb where charindex ( ' , ' + @str + ' , ' , ' , ' + TypeID + ' , ' ) > 0
select * from tb where ' , ' + TypeID + ' , ' like ' %, ' + @str + ' ,% '
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
(所影响的行数为 1 行)
*/
-- ---------------------------
-- 如果包含两个,如@str = '1,2'.
declare @str as varchar ( 30 )
set @str = ' 1,2 '
select * from tb where charindex ( ' , ' + left ( @str , charindex ( ' , ' , @str ) - 1 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + substring ( @str , charindex ( ' , ' , @str ) + 1 , len ( @str )) + ' , ' , ' , ' + typeid + ' , ' ) > 0
select * from tb where ' , ' + typeid + ' , ' like ' %, ' + left ( @str , charindex ( ' , ' , @str ) - 1 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + substring ( @str , charindex ( ' , ' , @str ) + 1 , len ( @str )) + ' ,% '
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
4 2,6
(所影响的行数为 3 行)
*/
-- -----------------------------------------
-- 如果包含三个或四个,用PARSENAME函数来处理.
declare @str as varchar ( 30 )
set @str = ' 1,2,3,4 '
select * from tb where
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 4 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 3 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 2 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 1 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0
select * from tb where
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 4 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 3 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 2 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 1 ) + ' ,% '
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
(所影响的行数为 5 行)
*/
-- -------------------------------------
-- 如果超过四个,则只能使用函数或动态SQL来分解并查询数据。
/*
名称:fn_split函数.
功能:实现字符串分隔功能的函数
*/
create function dbo.fn_split( @inputstr varchar ( 8000 ), @seprator varchar ( 10 ))
returns @temp table (a varchar ( 200 ))
as
begin
declare @i int
set @inputstr = rtrim ( ltrim ( @inputstr ))
set @i = charindex ( @seprator , @inputstr )
while @i >= 1
begin
insert @temp values ( left ( @inputstr , @i - 1 ))
set @inputstr = substring ( @inputstr , @i + 1 , len ( @inputstr ) - @i )
set @i = charindex ( @seprator , @inputstr )
end
if @inputstr <> ' / '
insert @temp values ( @inputstr )
return
end
go
-- 调用
declare @str as varchar ( 30 )
set @str = ' 1,2,3,4,5 '
select distinct m. * from tb m,
( select * from dbo.fn_split( @str , ' , ' )) n
where charindex ( ' , ' + n.a + ' , ' , ' , ' + m.typeid + ' , ' ) > 0
drop table tb
drop function dbo.fn_split
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
(所影响的行数为 5 行)
*/
-- ----------------------------------------
-- 使用动态SQL的语句。
declare @str varchar ( 200 )
declare @sql as varchar ( 1000 )
set @str = ' 1,2,3,4,5 '
set @sql = ' select ''' + replace ( @str , ' , ' , ''' as id union all select ''' )
set @sql = @sql + ''''
set @sql = ' select distinct a.* from tb a , ( ' + @sql + ' ) b where charindex( ' + ''' , '' + b.id + ' + ''' , ''' + ' , ' + ''' , '' + a.typeid + ' + ''' , ''' + ' ) > 0 '
exec ( @sql )
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
(所影响的行数为 5 行)
*/
标题:分解字符串并查询相关数据
作者:范中磊
说明:通过使用函数等方法分解字符串查询相关数据。
问题:通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
6 6,7
*/
-- ---------------------------
create table tb (ID int , TypeID varchar ( 30 ))
insert into tb values ( 1 , ' 1,2,3,4,5,6,7,8,9,10,11,12 ' )
insert into tb values ( 2 , ' 2,3 ' )
insert into tb values ( 3 , ' 3,7,8,9 ' )
insert into tb values ( 4 , ' 2,6 ' )
insert into tb values ( 5 , ' 4,5 ' )
insert into tb values ( 6 , ' 6,7 ' )
go
-- ---------------------------
-- 如果仅仅是一个,如@str = '1'.
declare @str as varchar ( 30 )
set @str = ' 1 '
select * from tb where charindex ( ' , ' + @str + ' , ' , ' , ' + TypeID + ' , ' ) > 0
select * from tb where ' , ' + TypeID + ' , ' like ' %, ' + @str + ' ,% '
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
(所影响的行数为 1 行)
*/
-- ---------------------------
-- 如果包含两个,如@str = '1,2'.
declare @str as varchar ( 30 )
set @str = ' 1,2 '
select * from tb where charindex ( ' , ' + left ( @str , charindex ( ' , ' , @str ) - 1 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + substring ( @str , charindex ( ' , ' , @str ) + 1 , len ( @str )) + ' , ' , ' , ' + typeid + ' , ' ) > 0
select * from tb where ' , ' + typeid + ' , ' like ' %, ' + left ( @str , charindex ( ' , ' , @str ) - 1 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + substring ( @str , charindex ( ' , ' , @str ) + 1 , len ( @str )) + ' ,% '
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
4 2,6
(所影响的行数为 3 行)
*/
-- -----------------------------------------
-- 如果包含三个或四个,用PARSENAME函数来处理.
declare @str as varchar ( 30 )
set @str = ' 1,2,3,4 '
select * from tb where
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 4 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 3 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 2 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0 or
charindex ( ' , ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 1 ) + ' , ' , ' , ' + typeid + ' , ' ) > 0
select * from tb where
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 4 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 3 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 2 ) + ' ,% ' or
' , ' + typeid + ' , ' like ' %, ' + parsename ( replace ( @str , ' , ' , ' . ' ) , 1 ) + ' ,% '
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
(所影响的行数为 5 行)
*/
-- -------------------------------------
-- 如果超过四个,则只能使用函数或动态SQL来分解并查询数据。
/*
名称:fn_split函数.
功能:实现字符串分隔功能的函数
*/
create function dbo.fn_split( @inputstr varchar ( 8000 ), @seprator varchar ( 10 ))
returns @temp table (a varchar ( 200 ))
as
begin
declare @i int
set @inputstr = rtrim ( ltrim ( @inputstr ))
set @i = charindex ( @seprator , @inputstr )
while @i >= 1
begin
insert @temp values ( left ( @inputstr , @i - 1 ))
set @inputstr = substring ( @inputstr , @i + 1 , len ( @inputstr ) - @i )
set @i = charindex ( @seprator , @inputstr )
end
if @inputstr <> ' / '
insert @temp values ( @inputstr )
return
end
go
-- 调用
declare @str as varchar ( 30 )
set @str = ' 1,2,3,4,5 '
select distinct m. * from tb m,
( select * from dbo.fn_split( @str , ' , ' )) n
where charindex ( ' , ' + n.a + ' , ' , ' , ' + m.typeid + ' , ' ) > 0
drop table tb
drop function dbo.fn_split
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
(所影响的行数为 5 行)
*/
-- ----------------------------------------
-- 使用动态SQL的语句。
declare @str varchar ( 200 )
declare @sql as varchar ( 1000 )
set @str = ' 1,2,3,4,5 '
set @sql = ' select ''' + replace ( @str , ' , ' , ''' as id union all select ''' )
set @sql = @sql + ''''
set @sql = ' select distinct a.* from tb a , ( ' + @sql + ' ) b where charindex( ' + ''' , '' + b.id + ' + ''' , ''' + ' , ' + ''' , '' + a.typeid + ' + ''' , ''' + ' ) > 0 '
exec ( @sql )
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
(所影响的行数为 5 行)
*/