取字符串

Question

网上搜索了半天，都没找到例子：（
有一张表A，有列c1
c1
company1:department1:id1:11  
company2:department2:id2:
company3:department3:id1
company1122:department1122:id1:4444想用一条sql 语句把company1:department1:id1，
company2:department2:id2，
company3:department3:id1，
company1122:department1122，字符串取出来，

Answer 1

declare @a table
(
c1 varchar(100)
)insert  into @a
select 'company1:department1:id1:11'   union 
select 'company2:department2:id2:' union
select 'company3:department3:id1'union
select 'company1122:department1122:id1:4444'select 
substring(c1+':',0,charindex(':',stuff(stuff(c1+':',charindex(':',c1+':'),1,'_'),charindex(':',stuff(c1+':',charindex(':',c1+':'),1,'_')),1,'_'))-1) 
from @a
结果：
company1:department1:id
company1122:department1122:id
company2:department2:id
company3:department3:id

Answer 2

使用substring函数来取，但是你取字符串的规律是什么？先说明白

Answer 3

SELECT substring(col001,1,patindex('%※%',col001)) as c1 FROM A 

Answer 4

更正：
SELECT substring(col001,1,(patindex('%:id%',col001)+3) as c1 FROM A

Answer 5

最后汇总的语句接用。      
declare @a table
(
    c1 varchar(100)
)insert  into @a
select 'company1:department1:id1:11'   union 
select 'company2:department2:id2:' union
select 'company3:department3:id1'union
select 'company1122:department1122:id1:4444'SELECT substring(c1,1,(patindex('%:id%',c1)+3)) from @a

Answer 6

      
declare @a table
(
    c1 varchar(100)
)insert  into @a
select 'company1:department1:id1:11'   union 
select 'company2:department2:id2:' union
select 'company3:department3:id1'union
select 'company1122:department1122:id1:4444'SELECT substring(c1,1,(patindex('%:id%',c1)+3)) from @a