----老大的一个例子---- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /*--字符串拆分函数分拆指定分隔符的的字符串,返回指定位置的字符--邹建 2004.04(引用请保留此信息)--*//*--调用示例SELECT dbo.f_splitSTR(userinfo,'|||', 1) as 姓名, dbo.f_splitSTR(userinfo,'|||', 2) as 性格, dbo.f_splitSTR(userinfo,'|||', 3) as 手机, dbo.f_splitSTR(userinfo,'|||', 4) as 地址, dbo.f_splitSTR(userinfo,'|||', 5) as 其他 FROM( SELECT userinfo = '张三|||温和|||手机|||通讯地址' )A --*/ ALTER FUNCTION dbo.f_splitSTR( @s varchar(8000), --要分拆的字符串 @split varchar(10), --数据分隔符 @pos int -- 取第几个 )RETURNS varchar(100) AS BEGIN DECLARE @splitlen int, @re varchar(100) SET @splitlen=LEN(@split+'a') - 2 WHILE CHARINDEX(@split,@s) > 0 AND @pos > 0 SELECT @re = LEFT(@s,CHARINDEX(@split,@s)-1), @s=STUFF(@s,1,CHARINDEX(@split, @s)+@splitlen,''), @pos = @pos - 1 RETURN(CASE WHEN @pos = 0 THEN @re WHEN @pos = 1 THEN @s ELSE NULL END) ENDGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
理解错了 邹键的例子 create function f_splitStr(@s varchar(8000),@split varchar(10)) returns @tb table(col varchar(100)) as begin declare @t table(id int identity,b bit) insert @t select top 500 0 from syscolumns a,syscolumns b insert @tb select substring(@s,id,charindex(@split,@s+@split,id)-id) from @t where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id return end goselect * from dbo.f_splitStr('a,b,c,a',',')
SQL中没提供拆分字符串的函数,要自己写存储过程create procedure split_str --要被分割的字符串 @sourceStr nvarchar(2000), --分隔标志名 @flagName nvarchar(2) as --临时表 declare @temp table(sname nvarchar(100)) --计数器 declare @i int --除去多余两边的空格 set @sourceStr = rtrim(ltrim(@sourceStr)) --获取索引 set @i=charindex(@flagName,@sourceStr) --遍历截取 while @i >= 1 begin --排除开始处有分隔字符的怀况 if(left(@sourceStr,@i-1) <> '') begin --查到一次填充一次 insert @temp values(left(@sourceStr,@i-1)) end -- 获取还未拆分的字符 set @sourceStr = substring(@sourceStr,@i+1,len(@sourceStr)-@i) -- 获取标记所在索引 set @i = charindex(@flagName,@sourceStr) end --保存最后一节非空字符 if(@sourceStr <> '') begin insert @temp values(@sourceStr) end select * from @temp GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*--字符串拆分函数分拆指定分隔符的的字符串,返回指定位置的字符--邹建 2004.04(引用请保留此信息)--*//*--调用示例SELECT
dbo.f_splitSTR(userinfo,'|||', 1) as 姓名,
dbo.f_splitSTR(userinfo,'|||', 2) as 性格,
dbo.f_splitSTR(userinfo,'|||', 3) as 手机,
dbo.f_splitSTR(userinfo,'|||', 4) as 地址,
dbo.f_splitSTR(userinfo,'|||', 5) as 其他
FROM(
SELECT userinfo = '张三|||温和|||手机|||通讯地址'
)A
--*/
ALTER FUNCTION dbo.f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10), --数据分隔符
@pos int -- 取第几个
)RETURNS varchar(100)
AS
BEGIN
DECLARE @splitlen int, @re varchar(100)
SET @splitlen=LEN(@split+'a') - 2
WHILE CHARINDEX(@split,@s) > 0 AND @pos > 0
SELECT
@re = LEFT(@s,CHARINDEX(@split,@s)-1),
@s=STUFF(@s,1,CHARINDEX(@split, @s)+@splitlen,''),
@pos = @pos - 1
RETURN(CASE
WHEN @pos = 0 THEN @re
WHEN @pos = 1 THEN @s
ELSE NULL END)
ENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
邹键的例子
create function f_splitStr(@s varchar(8000),@split varchar(10))
returns @tb table(col varchar(100))
as
begin
declare @t table(id int identity,b bit)
insert @t select top 500 0 from syscolumns a,syscolumns b insert @tb select substring(@s,id,charindex(@split,@s+@split,id)-id)
from @t
where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id return
end
goselect * from dbo.f_splitStr('a,b,c,a',',')
--要被分割的字符串
@sourceStr nvarchar(2000),
--分隔标志名
@flagName nvarchar(2)
as
--临时表
declare @temp table(sname nvarchar(100))
--计数器
declare @i int
--除去多余两边的空格
set @sourceStr = rtrim(ltrim(@sourceStr))
--获取索引
set @i=charindex(@flagName,@sourceStr)
--遍历截取
while @i >= 1
begin
--排除开始处有分隔字符的怀况
if(left(@sourceStr,@i-1) <> '')
begin
--查到一次填充一次
insert @temp values(left(@sourceStr,@i-1))
end
-- 获取还未拆分的字符
set @sourceStr = substring(@sourceStr,@i+1,len(@sourceStr)-@i)
-- 获取标记所在索引
set @i = charindex(@flagName,@sourceStr)
end
--保存最后一节非空字符
if(@sourceStr <> '')
begin
insert @temp values(@sourceStr)
end
select * from @temp
GO