if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufnSplitStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[ufnSplitStr] GO
/*--字符串分函数 --*/
CREATE FUNCTION ufnSplitStr( @s varchar(8000), --要分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END GO
drop proc InsertStr go create proc InsertStr ( @str nvarchar(300)--遍历的字符串 ) as--定义表变量 declare @tmp_TB table ( 内容 varchar(300) ) --内容 declare @Content varchar(300)while (charindex('|',@str)>0) begin set @Content=substring(@str,0,charindex('|',@str)) insert into @tmp_TB values(@Content) set @str=substring(@str,charindex('|',@str)+1,len(@str)-charindex('|',@str))endselect * from @tmp_TB go--执行语句 exec InsertStr @str='a|b|c|d|e|f|g|'
insert into a select col from (ufnSplitStr('a|b|c|d|e|f|g|','|'))
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufnSplitStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ufnSplitStr]
GO
/*--字符串分函数 --*/
CREATE FUNCTION ufnSplitStr(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
go
create proc InsertStr
(
@str nvarchar(300)--遍历的字符串
)
as--定义表变量
declare @tmp_TB table
(
内容 varchar(300)
)
--内容
declare @Content varchar(300)while (charindex('|',@str)>0)
begin
set @Content=substring(@str,0,charindex('|',@str))
insert into @tmp_TB values(@Content)
set @str=substring(@str,charindex('|',@str)+1,len(@str)-charindex('|',@str))endselect * from @tmp_TB
go--执行语句
exec InsertStr @str='a|b|c|d|e|f|g|'