declare @t table([id] int,[s] nvarchar(30))
Insert @t
select 6,N'A_dgdfdf_34' union all
select 2,N'B_dfgd_34543' union all
select 1,N'A_erdftr_675' union all
select 4,N'C_dfgdfd_57' union all
select 9,N'C_dfgd_34'select [id],left(s,charindex('_',s,charindex('_',s)+1)-1)from @t/*
6 A_dgdfdf
2 B_dfgd
1 A_erdftr
4 C_dfgdfd
9 C_dfgd
*/
Insert @t
select 6,N'A_dgdfdf_34' union all
select 2,N'B_dfgd_34543' union all
select 1,N'A_erdftr_675' union all
select 4,N'C_dfgdfd_57' union all
select 9,N'C_dfgd_34'
select id,left(s,charindex('_',s,3)-1) from @t
/*
id
----------- ------------------------------
6 A_dgdfdf
2 B_dfgd
1 A_erdftr
4 C_dfgdfd
9 C_dfgd
*/
(
[id] int,
[s] nvarchar(30)
)
Insert @t
select 6,N'A_dgdfdf_34' union all
select 2,N'B_dfgd_34543' union all
select 1,N'A_erdftr_675' union all
select 4,N'C_dfgdfd_57' union all
select 9,N'C_dfgd_34'select
id,
left(s,charindex('_',s,charindex('_',s) + 1) -1)
from @t
(
[id] int,
[s] nvarchar(30)
)
Insert @t
select 6,N'A_dgdfdf_34' union all
select 2,N'B_dfgd_34543' union all
select 1,N'A_erdftr_675' union all
select 4,N'C_dfgdfd_57' union all
select 9,N'C_dfgd_34'select
id,
--left(s,charindex('_',s,charindex('_',s) + 1) -1),
left(s,charindex('_',stuff(s,charindex('_',s),1,'A'))-1)
from @t
Insert @t
select 6,N'A_dgdfdf_34' union all
select 2,N'B_dfgd_34543' union all
select 1,N'A_erdftr_675' union all
select 4,N'C_dfgdfd_57' union all
select 9,N'C_dfgd_34'select [id],left(s,charindex('_',s,charindex('_',s)+1)-1)from @t/*
6 A_dgdfdf
2 B_dfgd
1 A_erdftr
4 C_dfgdfd
9 C_dfgd
*/
Insert @t
select 6,N'A_dgdfdf_34' union all
select 2,N'B_dfgd_34543' union all
select 1,N'A_erdftr_675' union all
select 4,N'C_dfgdfd_57' union all
select 9,N'C_dfgd_34_ddd'select [id],left(s,len(s)-charindex('_',reverse(s))) from @t
只有4楼提到用reverse其他好象都不是