34,575
社区成员
发帖
与我相关
我的任务
分享
declare @i int
declare @str varchar(max)
set @i=0
set @str='88266,张三,12,十六,齐亮,NULL'+','
select substring(@str,1,charindex(',',@str,1)-1),
substring(),substring()
declare @str varchar(max)
set @str='88266,张三,12,十六,齐亮,NULL'
select dbo.f_GetStr(@str,1,',') A,
dbo.f_GetStr(@str,2,',') B,
dbo.f_GetStr(@str,3,',') C,
dbo.f_GetStr(@str,4,',') D,
dbo.f_GetStr(@str,5,',') E,
dbo.f_GetStr(@str,6,',') F
/*
A B C D E F
88266 张三 12 十六 齐亮 NULL
*/
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
这个好像比较实用,推荐一下
declare @i int ,@x int
declare @sql varchar(1000)
declare @str varchar(max)
set @str='''88266'',''张三'',''12'',''十六'',''齐亮'',NULL'
select @x=0,@i=len(@str)-len(replace(@str+',',',',''))
while @x<=@i
begin
select @sql=isnull(@sql+',','')+'[列'+ltrim(@x)+'] varchar(100)'
set @x=@x+1
end
exec('declare @tb table('+@sql+')
insert @tb select '+@str+'
select * from @tb')
-- 投机取巧
declare @str varchar(max);
set @str='''88266'',''张三'',''12'',''十六'',''齐亮'',NULL';
exec('select '+@str);
/*
88266 张三 12 十六 齐亮 NULL
*/
create PROC pp_ @str VARCHAR(1000)
AS
IF OBJECT_ID('[Table]') IS NOT NULL
DROP TABLE [Table]
declare @i int ,@x int
declare @sql varchar(1000)
select @x=0,@i=len(@str)-len(replace(@str+',',',',''))
while @x<=@i
begin
select @sql=isnull(@sql+',','')+'[列'+ltrim(@x)+'] varchar(100)'
set @x=@x+1
end
exec('create table [Table]('+@sql+')')
exec('insert [Table] select '+@str)
select * from [Table]
GO
declare @str varchar(1000)
set @str='''88266'',''张三'',''12'',''十六'',''齐亮'',NULL'
exec pp_ @str
--result
/*
列0 列1 列2 列3 列4 列5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
88266 张三 12 十六 齐亮 NULL
(所影响的行数为 1 行)
*/
declare @i int ,@x int
declare @sql varchar(1000)
declare @str varchar(max)
set @str='''88266'',''张三'',''12'',''十六'',''齐亮'',NULL'
select @x=0,@i=len(@str)-len(replace(@str+',',',',''))
while @x<=@i
begin
select @sql=isnull(@sql+',','')+'[列'+ltrim(@x)+'] varchar(100)'
set @x=@x+1
end
exec('create table [Table]('+@sql+')')
exec('insert [Table] select '+@str)
select * from [Table]