34,590
社区成员
发帖
与我相关
我的任务
分享
declare @revalue varchar(50)
declare @bar varchar(100),@col1 varchar(50),@col2 varchar(50),@col3 varchar(50)
select @bar = bar,@col1= col1,@col2 = col2, @col3 = col3 from t_bar
declare @PreU int, @CurU int,@str varchar(50)
set @str = ltrim(rtrim(@col1))
set @PreU = 0
set @CurU = charindex(':', @str+':')
declare @PreU1 int , @CurU1 int ,@str1 varchar(50)
set @str1 = ltrim(rtrim(@col2))
set @PreU1 = 0
set @CurU1 = charindex(':', @str1+':')
declare @PreU2 int, @CurU2 int ,@str2 varchar(50)
set @str2 = ltrim(rtrim(@col3))
set @PreU2= 0
set @CurU2 = charindex(':', @str2+':')
declare @name varchar(50),@name1 varchar(50),@name2 varchar(50)
declare @revalue1 varchar(50)
set @revalue1=''
while (@CurU>0)
begin
set @name = substring(@str, @PreU+1, @CurU - @PreU -1)
set @name1 = substring(@str1, @PreU1+1, @CurU1 - @PreU1 -1)
set @name2 = substring(@str2, @PreU2+1, @CurU2 - @PreU2 -1)
if(@name<>'')
begin
--增加當前問題的答案
set @revalue1 = @revalue1+ @name+':'+@name1+':'+@name2 +'/';
end
set @PreU = @CurU
set @CurU = charindex(':', @str+':', @CurU+1)
set @PreU1 = @CurU1
set @CurU1 = charindex(':', @str1+':', @CurU1+1)
set @PreU2 = @CurU2
set @CurU2 = charindex(':', @str2+':', @CurU2+1)
end
set @revalue1 = @bar + ' '+@revalue1
set @revalue = @revalue1
--print @revalue
select @revalue
你可以把上面的放在一个函数里面,太多了,刚刚想的,
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-12 11:50:08
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([Bar] varchar(1),[col1] varchar(11),[col2] varchar(11),[col3] varchar(8))
insert [TB]
select 'A','1:2:3','100:200:230','23:45:80'
--------------开始查询--------------------------
select Bar,
SUBSTRING(T.col1,NUMBER,CHARINDEX(':',T.col1+':',NUMBER)-NUMBER)AS COL1,1 ID
from [TB] T,MASTER..SPT_VALUES WHERE TYPE='P'
AND SUBSTRING(':'+T.col1,NUMBER,1)=':'
select Bar,
SUBSTRING(T.col2,NUMBER,CHARINDEX(':',T.col2+':',NUMBER)-NUMBER)AS COL2,2 ID
from [TB] T,MASTER..SPT_VALUES WHERE TYPE='P'
AND SUBSTRING(':'+T.coL2,NUMBER,1)=':'
select Bar,
SUBSTRING(T.col3,NUMBER,CHARINDEX(':',T.col3+':',NUMBER)-NUMBER)AS COL3,3 ID
from [TB] T,MASTER..SPT_VALUES WHERE TYPE='P'
AND SUBSTRING(':'+T.coL3,NUMBER,1)=':'
----------------结果----------------------------
/*
(所影响的行数为 1 行)
Bar COL1 ID
---- ----------- -----------
A 1 1
A 2 1
A 3 1
(所影响的行数为 3 行)
Bar COL2 ID
---- ----------- -----------
A 100 2
A 200 2
A 230 2
(所影响的行数为 3 行)
Bar COL3 ID
---- -------- -----------
A 23 3
A 45 3
A 80 3
(所影响的行数为 3 行)
*/
create table tb(Bar varchar(10),col1 varchar(50), col2 varchar(50), col3 varchar(50))
insert into tb values('A' , '1:2:3', '100:200:230', '23:45:80')
go
select bar,
parsename(replace(col1,':','.'),3) + ':' +
parsename(replace(col2,':','.'),3) + ':' +
parsename(replace(col3,':','.'),3) + '/' +
parsename(replace(col1,':','.'),2) + ':' +
parsename(replace(col2,':','.'),2) + ':' +
parsename(replace(col3,':','.'),2) + '/' +
parsename(replace(col1,':','.'),1) + ':' +
parsename(replace(col2,':','.'),1) + ':' +
parsename(replace(col3,':','.'),1)
from tb
drop table tb
/*
bar
---------- --------------------------
A 1:100:23/2:200:45/3:230:80
(所影响的行数为 1 行)
*/
select [Bar],
dbo.getitem([Bar],1)+'/'+
dbo.getitem([Bar],2)+'/'+
dbo.getitem([Bar],3)
from [tb]
----------------------------
A 1:100:23/2:200:45/3:230:80
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([Bar] varchar(1),[col1] varchar(11),[col2] varchar(11),[col3] varchar(8))
insert [tb]
select 'A','1:2:3','100:200:230','23:45:80'
--函数
alter function dbo.getitem(@bar varchar(30),@pos int)
returns varchar(100)
as
begin
declare @ret varchar(100)
declare @col1 table(id int identity(1,1),name varchar(300))
insert into @col1
select substring(t.[col1],r.number,charindex(':',t.[col1]+':',r.number)-r.number)
from master..spt_values r,[tb] t
where r.type='p'
and substring(':'+t.[col1],r.number,1)=':'
and t.[Bar]=@bar
declare @col2 table(id int identity(1,1),name varchar(300))
insert into @col2
select substring(t.[col2],r.number,charindex(':',t.[col2]+':',r.number)-r.number)
from master..spt_values r,[tb] t
where r.type='p'
and substring(':'+t.[col2],r.number,1)=':'
and t.[Bar]=@bar
declare @col3 table(id int identity(1,1),name varchar(300))
insert into @col3
select substring(t.[col3],r.number,charindex(':',t.[col3]+':',r.number)-r.number)
from master..spt_values r,[tb] t
where r.type='p'
and substring(':'+t.[col3],r.number,1)=':'
and t.[Bar]=@bar
select @ret=''
select @ret=r.name+':'+t.name+':'+h.name
from @col1 r,@col2 t,@col3 h
where r.id = t.id and t.id=h.id and
r.id=@pos
return @ret
end
--查询
select [Bar],
dbo.getitem([Bar],1),
dbo.getitem([Bar],2),
dbo.getitem([Bar],3)
from [tb]
--结果
----------------------------
A 1:100:23 2:200:45 3:230:80