请参考slot=0|subslot=0|port=1|vpi=|vci=|vlan=525|--将PVC的字段分开
select Col001 As Account,Col009 As BAS_IP,
Substring(Col002,charindex('slot=',Col002,1)+5,charindex('|subslot',Col002,1)-charindex('slot=',Col002,1)-5) as Slot,
Substring(Col002,charindex('subslot=',Col002,1)+8,charindex('|port',Col002,1)-charindex('subslot=',Col002,1)-8) as Subslot,
Substring(Col002,charindex('port=',Col002,1)+5,charindex('|vpi',Col002,1)-charindex('port=',Col002,1)-5) as Port,
Substring(Col002,charindex('vpi=',Col002,1)+4,charindex('|vci',Col002,1)-charindex('vpi=',Col002,1)-4) as Vpi,
Substring(Col002,charindex('vci=',Col002,1)+4,charindex('|vlan',Col002,1)-charindex('vci=',Col002,1)-4) as Vci,
Substring(Col002,charindex('vlan=',Col002,1)+5,len(Col002)-charindex('vlan=',Col002,1)-5) as Vlan
from Newtable
select Col001 As Account,Col009 As BAS_IP,
Substring(Col002,charindex('slot=',Col002,1)+5,charindex('|subslot',Col002,1)-charindex('slot=',Col002,1)-5) as Slot,
Substring(Col002,charindex('subslot=',Col002,1)+8,charindex('|port',Col002,1)-charindex('subslot=',Col002,1)-8) as Subslot,
Substring(Col002,charindex('port=',Col002,1)+5,charindex('|vpi',Col002,1)-charindex('port=',Col002,1)-5) as Port,
Substring(Col002,charindex('vpi=',Col002,1)+4,charindex('|vci',Col002,1)-charindex('vpi=',Col002,1)-4) as Vpi,
Substring(Col002,charindex('vci=',Col002,1)+4,charindex('|vlan',Col002,1)-charindex('vci=',Col002,1)-4) as Vci,
Substring(Col002,charindex('vlan=',Col002,1)+5,len(Col002)-charindex('vlan=',Col002,1)-5) as Vlan
from Newtable
select top 1 @s=col from table
set @s=replace(@s,'/',',')exec(@s)
declare @s varchar(20)
set @s='1/2/3/4....'
select col1=left(@s,charindex('/',@s)-1)
,col2=left(substring(@s,charindex('/',@s)+1,len(@s)),charindex('/',substring(@s,charindex('/',@s)+1,len(@s)))-1)
,col3=left(substring(substring(@s,charindex('/',@s)+1,len(@s)),charindex('/',substring(@s,charindex('/',@s)+1,len(@s)))+1,len(substring(@s,charindex('/',@s)+1,len(@s)))),charindex('/',substring(substring(@s,charindex('/',@s)+1,len(@s)),charindex('/',substring(@s,charindex('/',@s)+1,len(@s)))+1,len(substring(@s,charindex('/',@s)+1,len(@s)))))-1)
--结果:
col1 col2 col3
-------------------- -------------------- --------------------
1 2 3
col1=left(@s,charindex('/',@s)-1) ---第一你要显示的值,也就是第一个'/'前的值。
那么col2时,主要把col1里的@s替换成substring(@s,charindex('/',@s)+1,len(@s))
@s2=substring(@s,charindex('/',@s)+1,len(@s))
那么col3时,再把col2里的@s替换成substring(@s,charindex('/',@s)+1,len(@s))
@s3=substring(@s2,charindex('/',@s2)+1,len(@s2))
真是罗嗦啦,呵呵