参考 /* 名称: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'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO --分段截取函数 CREATE FUNCTION dbo.f_GetStr( @s varchar(8000), --包含多个数据项的字符串 @pos int, --要获取的数据项的位置 @split varchar(10) --数据分隔符 )RETURNS varchar(100) AS BEGIN IF @s IS NULL RETURN(NULL) DECLARE @splitlen int SELECT @splitlen=LEN(@split+'a')-2 WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0 SELECT @pos=@pos-1, @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'') RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),'')) END GO select [dbo].[f_GetStr]('11&Red&12&',1,'&') select [dbo].[f_GetStr]('11&Red&12&',2,'&') select [dbo].[f_GetStr]('11&Red&12&',3,'&') /* ---------------------------------------------------------------------------------------------------- 11 ---------------------------------------------------------------------------------------------------- Red ---------------------------------------------------------------------------------------------------- 12 */
--轉老大 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO --分段截取函数 CREATE FUNCTION dbo.f_GetStr( @s varchar(8000), --包含多个数据项的字符串 @pos int, --要获取的数据项的位置 @split varchar(10) --数据分隔符 )RETURNS varchar(100) AS BEGIN IF @s IS NULL RETURN(NULL) DECLARE @splitlen int SELECT @splitlen=LEN(@split+'a')-2 WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0 SELECT @pos=@pos-1, @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'') RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),'')) END GOselect dbo.f_GetStr('11&Red&12&',1,'&'),dbo.f_GetStr('11&Red&12&',2,'&'),dbo.f_GetStr('11&Red&12&',3,'&') /*---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 11 Red 12(1 行受影响) */
declare @s1 varchar(8000) set @s1 = '11&Red&12&'select * from f_split(@s1,'&')/** a --------------------------------- 11 Red 12 (所影响的行数为 4 行) **/
看下面的分解即可. /* 标题:分解字符串并查询相关数据 作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间:2008-03-18 地点:广东深圳 说明:通过使用函数等方法分解字符串查询相关数据。问题:通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。 例如 @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 + ',') > 0drop 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 行) */
/*
名称: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'
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
select [dbo].[f_GetStr]('11&Red&12&',1,'&')
select [dbo].[f_GetStr]('11&Red&12&',2,'&')
select [dbo].[f_GetStr]('11&Red&12&',3,'&')
/*
----------------------------------------------------------------------------------------------------
11
----------------------------------------------------------------------------------------------------
Red
----------------------------------------------------------------------------------------------------
12
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GOselect dbo.f_GetStr('11&Red&12&',1,'&'),dbo.f_GetStr('11&Red&12&',2,'&'),dbo.f_GetStr('11&Red&12&',3,'&')
/*---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
11 Red 12(1 行受影响)
*/
set @s1 = '11&Red&12&'select * from f_split(@s1,'&')/**
a
---------------------------------
11
Red
12
(所影响的行数为 4 行)
**/
/*
标题:分解字符串并查询相关数据
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-03-18
地点:广东深圳
说明:通过使用函数等方法分解字符串查询相关数据。问题:通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @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 + ',') > 0drop 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 行)
*/
select replace('11&Red&12&','&',',')11,Red,12,
(1 行受影响)
,[dbo].[f_GetStr]('11&Red&12&',2,'&')
,[dbo].[f_GetStr]('11&Red&12&',3,'&')
godeclare @s as varchar(100)
set @s = '11&Red&12'set @s = 'insert into tb values(' + '''' + replace(@s , '&' , ''' , ''') + '''' + ')'exec(@s)select * from tbdrop table tb/*
a b c
---------- ---------- ----------
11 Red 12(所影响的行数为 1 行)*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(20),[b] varchar(20),[c] varchar(20))declare @str varchar(100)
select @str = '11&Red&12&'
select @str = left(replace(@str,'&',''','''),len(replace(@str,'&',''','''))-2)exec( 'insert into tb(a,b,c) select '''+@str)select * from [tb]
----------------------------
11 Red 12