create table #tb(f1 varchar(100))
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8'
给定一个字符串如下
A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S
要求按照#tb表中f1字段的值对字符串相应位置取值,各个值以空格分开
对应关系:
比如'5,8,9,10,13' 取字符串A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S 第5,8,9,10,13个位置的对应值 结果 9 Y - X 0 (值与值间加空格)上面字符串的最后结果如下:
9 Y - X 0
9 Y X + 0
9 - X + 0
2 M Y - .
A D 5 9 .
A D 9 M Y
请用sql2000实现
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8'
给定一个字符串如下
A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S
要求按照#tb表中f1字段的值对字符串相应位置取值,各个值以空格分开
对应关系:
比如'5,8,9,10,13' 取字符串A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S 第5,8,9,10,13个位置的对应值 结果 9 Y - X 0 (值与值间加空格)上面字符串的最后结果如下:
9 Y - X 0
9 Y X + 0
9 - X + 0
2 M Y - .
A D 5 9 .
A D 9 M Y
请用sql2000实现
看不懂
liangCK
帮写个sql2000的吧
insert into tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8'
gocreate function my_func (@f1 varchar(50))
RETURNS varchar(50)
AS
BEGIN
declare @str varchar(50)
declare @rtn varchar(50)
declare @i as int
set @str = 'A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S'
set @rtn = ''
set @i = 0
while charindex(',',@f1) > 0
begin
set @i = left(@f1 , charindex(',',@f1) - 1)
set @rtn = @rtn + ' ' + substring(@str , (@i-1)*2+1 , 1)
set @f1 = substring(@f1 , charindex(',',@f1) + 1 , len(@f1))
end
set @i = @f1
set @rtn = @rtn + ' ' + substring(@str , (@i-1)*2+1 , 1)
RETURN(@rtn)
END
goselect f1 , f2 = dbo.my_func(f1) from tbdrop table tb
drop function dbo.my_func/*
f1 f2
---------------------------------------------------------------------------------------------------- --------------------------------------------------
5,8,9,10,13 9 Y - X 0
5,8,10,12,13 9 Y X + 0
5,9,10,12,13 9 - X + 0
6,7,8,9,11 2 M Y - .
1,3,4,5,11 A D 5 9 .
2,4,5,6,7 N 5 9 2 M
1,3,5,7,8 A D 9 M Y(所影响的行数为 7 行)*/
if object_id('ta') is not null drop table ta
go
create table ta(id int identity(1,1),dd varchar(1))
insert ta values('A')
insert ta values('N')
insert ta values('D')
insert ta values('5')
insert ta values('9')
insert ta values('2')
insert ta values('M')
insert ta values('Y')
insert ta values('-')
insert ta values('X')
insert ta values('.')
insert ta values('+')
insert ta values('0')
insert ta values('4')
insert ta values('S')
go
select * from ta
----创建函数
create function fn_getString(@str varchar(50))
returns varchar(50)
as
begin
declare @R varchar(50)
select @R = isnull(@R,'')+dd from ta where charindex(','+rtrim(id)+',',','+@str+',')>0
return @R
endselect *,rr = dbo.fn_getString(f1) from #tb
/*
f1 rr
---------------------------------------------------------------------------------------------------- --------------------------------------------------
5,8,9,10,13 9Y-X0
5,8,10,12,13 9YX+0
5,9,10,12,13 9-X+0
6,7,8,9,11 2MY-.
1,3,4,5,11 AD59.
2,4,5,6,7 N592M
1,3,5,7,8 AD9MY(7 row(s) affected)
*/
----创建函数
alter function fn_getString(@str varchar(50))
returns varchar(50)
as
begin
declare @R varchar(50)
select @R = isnull(@R,'')+dd+' ' from ta where charindex(','+rtrim(id)+',',','+@str+',')>0
return @R
endselect *,rr = dbo.fn_getString(f1) from #tb
/*
f1 rr
f1 rr
---------------------------------------------------------------------------------------------------- --------------------------------------------------
5,8,9,10,13 9 Y - X 0
5,8,10,12,13 9 Y X + 0
5,9,10,12,13 9 - X + 0
6,7,8,9,11 2 M Y - .
1,3,4,5,11 A D 5 9 .
2,4,5,6,7 N 5 9 2 M
1,3,5,7,8 A D 9 M Y (7 row(s) affected)*/
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @t TABLE(id int identity,data VARCHAR(10))
DECLARE @result VARCHAR(50)
INSERT @t(data)
SELECT
SUBSTRING(@s2,number,CHARINDEX(',',@s2+',',number)-number)
FROM master.dbo.spt_values
WHERE type='p'
AND number BETWEEN 1 AND LEN(@s2)
AND SUBSTRING(','+@s2,number,1)=','
SET @result=''
SELECT
@result=@result+' '+data
FROM @t
WHERE CHARINDEX(','+RTRIM(id)+',',','+@s1+',')>0
RETURN STUFF(@result,1,1,'');
END
GOcreate table #tb(f1 varchar(100))
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8' SELECT
f1,
dbo.getdata(f1,'A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S')
FROM #tbGO
DROP TABLE #tb
DROP FUNCTION dbo.getdata