select Gid, one,two,rtrim(ltrim(replace(substring(rightPart,charindex(';',rightPart)+1,charindex(';',rightPart,charindex(';',rightPart))),';',''))) three,main from (select
replace(ltrim(Rtrim(substring(cast([主营产品或服务] as varchar(1200)),1,charindex(';',cast([主营产品或服务] as varchar(1200)))))),';','') one
,left(right(cast([主营产品或服务] as varchar(1200)),len(cast([主营产品或服务] as varchar(1200)))- charindex(';',cast([主营产品或服务] as varchar(1200)))),dbo.CheckNum(charindex(';',right(cast([主营产品或服务] as varchar(1200)),len(cast([主营产品或服务] as varchar(1200)))- charindex(';',cast([主营产品或服务] as varchar(1200)))))-1)) two
,len(cast([主营产品或服务] as varchar(1200)))- charindex(';',cast([主营产品或服务] as varchar(1200))) three
,right(cast([主营产品或服务] as varchar(1200)),len(cast([主营产品或服务] as varchar(1200)))- charindex(';',cast([主营产品或服务] as varchar(1200)))) rightPart
,len(cast([主营产品或服务] as varchar(1200))) Clength
,[主营产品或服务] main,Gid
from companys) tab
