不好意思,没有描述清楚,我的目的就是想要得到一个文本,有个值是安顺序取自sConsNewServiceRight的一位,如下面这个例子,但是这个字段有49位,我想不出办法来。
例子:
select
sCallOutPur,"OCR=LCO-" ,left(sCallOutPur,1),
"&LC-",right(left(sCallOutPur,2),1),
"&LCT-" ,right(left (sCallOutPur,3),1),
"&NTT-" ,right(left(sCallOutPur,4),1),
"&=ITT-" ,right(left(sCallOutPur,5),1),
"&ICTX-",right(left(sCallOutPur,6),1),
"&OCTX-",right(left(sCallOutPur,7),1)
from tbl_V5STUserData 返回的结果:
sCallOutPur
-----------
1111011 OCR=LCO- 1 &LC- 1 &LCT- 1 &NTT- 1 &=ITT- 0 &ICTX- 1 &OCTX- 1
1111011 OCR=LCO- 1 &LC- 1 &LCT- 1 &NTT- 1 &=ITT- 0 &ICTX- 1 &OCTX- 1
例子:
select
sCallOutPur,"OCR=LCO-" ,left(sCallOutPur,1),
"&LC-",right(left(sCallOutPur,2),1),
"&LCT-" ,right(left (sCallOutPur,3),1),
"&NTT-" ,right(left(sCallOutPur,4),1),
"&=ITT-" ,right(left(sCallOutPur,5),1),
"&ICTX-",right(left(sCallOutPur,6),1),
"&OCTX-",right(left(sCallOutPur,7),1)
from tbl_V5STUserData 返回的结果:
sCallOutPur
-----------
1111011 OCR=LCO- 1 &LC- 1 &LCT- 1 &NTT- 1 &=ITT- 0 &ICTX- 1 &OCTX- 1
1111011 OCR=LCO- 1 &LC- 1 &LCT- 1 &NTT- 1 &=ITT- 0 &ICTX- 1 &OCTX- 1
set @i=0
select substring(sCallOutPur,@i+1,1)
来查字段sCallOutPur的第i个字符
create function f_str(
@str char(49)
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+' '+L+substring(@str,id,1)
from(
select L='OCR=LCO-',id=1
union all select '&LC-',2
union all select '&LCT-',3
union all select '&NTT-',4
union all select '&=ITT-',5
union all select '&ICTX-',6
union all select '&OCTX-',7 --后面的数据继续写下去
)a
return(stuff(@re,1,1,''))
end
go--调用自定义函数实现查询
select sCallOutPur,dbo.f_str(sCallOutPur)
from tbl_V5STUserData