在线求一个SQL语句
我有一个字段,假设为TYPE,里面的值是这样的:

9,A4,0|10,A6,0|15,T7,0|20,TS,0|

现求SQL语句实现以下功能:

传入一个值,

假设为TS,我要取得相对应的值是:

20,TS,0里面的20.

假设为A6,我要取得相对应的值是:

10,A6,0里面的10.

谢谢!!

[解决办法]
SELECT left(type,PATINDEX( '%,% ', type)-1)
FROM Table1
WHERE (type LIKE '%a6% ')
[解决办法]
'%a6% '，替换这里的值即可。
[解决办法]
create function fn_fenli( @q nvarchar(20))
returns nvarchar(20)
as
begin
declare @a nvarchar(100)

set @A= '9,A4,0|10,A6,0|15,T7,0|20,TS,0| '
set @A= '| '+@A
declare @b nvarchar(100)
select @b=reverse(substring(@A,1,charindex(@Q,@a)-2))
return reverse(substring(@b,1,charindex( '| ',@b)-1))

end


select dbo.fn_fenli( 'ts '),dbo.fn_fenli( 't7 '),dbo.fn_fenli( 'a4 ')



-------------------- -------------------- --------------------
20 15 9

(1 row(s) affected)

[解决办法]
declare @a varchar(100),@b varchar(10),@Value varchar(10)
set @a= '9,A4,0|10,A6,0|15,T7,0|20,TS,0 '
set @b= 'TS '

select top 100 id=identity(int,1,1) ,1 a into # from syscolumns

select @Value=left(aa,charindex( ', '+@b,aa)-1) from
(select substring(@a+ '| ',id,charindex( '| ',@a+ '| ',id+1)-id) aa
from #
where substring( '| '+@a,id,1)= '| '
)T where charindex( ', '+@b,aa)> 0

select @Value
drop table #
[解决办法]
text这样算肯定不行，你的表是什么样的，会不会传一个值进去得到多个记录
[解决办法]
字段类型是什么

[解决办法]
declare @T varchar(500)
declare @Ti varchar(10)
declare @T_SQL varchar(8000)
set @T= '9,A4,0|10,A6,0|15,T7,0|20,TS,0| '
set @Ti= 'T7 '
if charindex( '| ',@T)=1
set @T=STUFF(@T,1,1, ' ')

if charindex( '| ',REVERSE(@T))=1
set @T=REVERSE(STUFF(REVERSE(@T),1,1, ' '))

set @T_SQL= ' '
select @T_SQL= 'select left(A,charindex( ' ', ' ',A)-1) from (select ' ' ' + REPLACE(@T, '| ', ' ' ' as ' 'A ' ' union all select ' ' ') + ' ' ' as ' 'A ' ') T where charindex( ' ' '+ @Ti + ' ' ',A)> 0 '
print @T_SQL
exec (@T_SQL)
[解决办法]
create function fn_fenli( @q nvarchar(20))
returns nvarchar(20)
as
begin
declare @a nvarchar(100),@b nvarchar(100)
select @A= ' ',@b= ' '

select @A=Convert(nvarchar(4000),TYPE)
from table1
where charindex(@q,Convert(nvarchar(4000),TYPE))> 0


if @A <> ' '
begin
set @A= '| '+@A

select @b=reverse(substring(@A,1,charindex(@Q,@a)-2))
select @b=reverse(substring(@b,1,charindex( '| ',@b)-1))
end

return @b
end

这样试试

[解决办法]
create table T
(
T text
)
insert T select '9,A4,0|10,A6,0|15,T7,0|20,TS,0| '

create proc Te(@Ti varchar(10))
as
begin
declare @T varchar(8000)
select @T=cast(T as varchar) from T --where 条件
declare @T_SQL varchar(8000)
if charindex( '| ',@T)=1
set @T=STUFF(@T,1,1, ' ')

if charindex( '| ',REVERSE(@T))=1
set @T=REVERSE(STUFF(REVERSE(@T),1,1, ' '))
set @T_SQL= ' '
select @T_SQL= 'select left(A,charindex( ' ', ' ',A)-1) from (select ' ' ' + REPLACE(@T, '| ', ' ' ' as ' 'A ' ' union all select ' ' ') + ' ' ' as ' 'A ' ') T where charindex( ' ' '+ @Ti + ' ' ',A)> 0 '
exec (@T_SQL)
end

exec Te 'TS '
[解决办法]
create table a(a int,b text)
insert a select 1, '9,B8,0|450,A6,0|15,TT,0|20,XU,0 '
insert a select 2, '9,A8,0|10,A6,0|15,MN,0|20,UU,0 '

go
create function getValue(@a int,@v varchar(20))
returns varchar(100)
as
begin
declare @x varchar(100)
declare @M int,@I int,@N int

if exists(select 1 from a where a=@a and charindex(@v,b)=0)
set @x= ' '
else
begin
select @M=charindex(@v,b),@I=charindex( '| ',b) from a where a=@a
set @N=@I
while @I <@M
begin
select @I=charindex( '| ',b,@i+1) from a where a=@a
if @I=0 or @I> @M
goto X
else
set @N=@I
end
X:
select @x=substring(b,@N+1,@M-@N-2) from a where a=@a
end
return @x
end

go
select a,b,dbo.getValue(a, 'TT ') from a
[解决办法]
--result
/*
a b
----------- ------------------------------ ------------------------------
1 9,B8,0|450,A6,0|15,TT,0|20,XU, 15
2 9,A8,0|10,A6,0|15,MN,0|20,UU,0

（所影响的行数为 2 行）
*/
[解决办法]
在set @N=@I下面加上:
if @I> @M
begin
select @N=0
goto X
end
[解决办法]
学习