这个csdn编辑器,真的不好,排版得好好的,提交后,就变成这样了 有一数据列:PKID aID 1 1,2,3,4要得出以下结果:(用逗号分隔的值,要形成以下的字段列): PKID aID Temp1 Temp2 Temp3 Temp4 Temp9 1 1,2,3,4,9 1 2 3 4 9
declare @table table (PKID int,aID varchar(20)) insert into @table select 1,'1,2,3,4'select *, parsename(replace(aID,',','.'),4), parsename(replace(aID,',','.'),3), parsename(replace(aID,',','.'),2), parsename(replace(aID,',','.'),1) from @table
我是来看后面那些TEMP字段如果可以搞出来
create table t1 ( id1 int, id2 varchar(20) ) insert into t1 select 1, '1,2,3,4,9' select * from t1declare @sql varchar(8000), @id varchar(20), @len int set @id=(select id2 from t1 where id1=1) set @len=CHARINDEX(',',@id) set @sql='select id1,id2,' while @len>0 begin set @sql=@sql+LEFT(@id,@len-1)+' as Temp'+LEFT(@id,@len-1)+',' set @id=RIGHT(@id,LEN(@id)-@len) set @len=CHARINDEX(',',@id) end set @sql=@sql+@id+' as Temp'+@id+' from t1' print @sql exec (@sql)单行数据可以这么做,多行的话,我还没想过~~
--轉老大 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('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮') /* ---------------------------------------------------------------------------------------------------- 786(1 個資料列受到影響)
CREATE TABLE [dbo].[a]( [pid] [int] NULL, [aid] [nvarchar](50) NULL ) ON [PRIMARY] go insert into a(pid,aid) values (1,'1,12,23,334,94') go CREATE TABLE [dbo].[nums]( [n] [int] NULL ) ON [PRIMARY]GO declare @n1 int set @n1=1 while (@n1<1000) begin insert into nums(n) values (@n1) set @n1=@n1+1 end go select pid,aid,n, substring(aid+',',n,charindex(',',aid+',',n)-n) from a cross join nums where substring(','+aid,n,1)=',' gopid aid n (No column name) 1 1,12,23,334,94 1 1 1 1,12,23,334,94 3 12 1 1,12,23,334,94 6 23 1 1,12,23,334,94 9 334 1 1,12,23,334,94 13 94
有一数据列:PKID aID
1 1,2,3,4要得出以下结果:(用逗号分隔的值,要形成以下的字段列):
PKID aID Temp1 Temp2 Temp3 Temp4 Temp9
1 1,2,3,4,9 1 2 3 4 9
declare @table table (PKID int,aID varchar(20))
insert into @table
select 1,'1,2,3,4'select *,
parsename(replace(aID,',','.'),4),
parsename(replace(aID,',','.'),3),
parsename(replace(aID,',','.'),2),
parsename(replace(aID,',','.'),1)
from @table
create table t1
(
id1 int,
id2 varchar(20)
)
insert into t1
select 1, '1,2,3,4,9'
select * from t1declare @sql varchar(8000),
@id varchar(20),
@len int
set @id=(select id2 from t1 where id1=1)
set @len=CHARINDEX(',',@id)
set @sql='select id1,id2,'
while @len>0
begin
set @sql=@sql+LEFT(@id,@len-1)+' as Temp'+LEFT(@id,@len-1)+','
set @id=RIGHT(@id,LEN(@id)-@len)
set @len=CHARINDEX(',',@id)
end
set @sql=@sql+@id+' as Temp'+@id+' from t1'
print @sql
exec (@sql)单行数据可以这么做,多行的话,我还没想过~~
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('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
/*
----------------------------------------------------------------------------------------------------
786(1 個資料列受到影響)
CREATE TABLE [dbo].[a](
[pid] [int] NULL,
[aid] [nvarchar](50) NULL
) ON [PRIMARY]
go
insert into a(pid,aid) values (1,'1,12,23,334,94')
go
CREATE TABLE [dbo].[nums](
[n] [int] NULL
) ON [PRIMARY]GO
declare @n1 int
set @n1=1
while (@n1<1000)
begin
insert into nums(n) values (@n1)
set @n1=@n1+1
end
go
select pid,aid,n,
substring(aid+',',n,charindex(',',aid+',',n)-n)
from a cross join nums where substring(','+aid,n,1)=','
gopid aid n (No column name)
1 1,12,23,334,94 1 1
1 1,12,23,334,94 3 12
1 1,12,23,334,94 6 23
1 1,12,23,334,94 9 334
1 1,12,23,334,94 13 94