create table tb(col varchar(20)) insert into tb values('SN0809001_1') insert into tb values('AB08001_1_1') insert into tb values('ABC_2') insert into tb values('SN0809001_2_1') goselect col = left(col , charindex('_',col) - 1) from tb where charindex('_',col) > 0drop table tb/* col -------------------- SN0809001 AB08001 ABC SN0809001(所影响的行数为 4 行) */
select Substring(字段,0,CharIndex('_',字段))
SELECT LEFT(字段+'_',charindex('_',字段)) from 表--要防止没有_的情况-1时报错
SELECT LEFT(字段,charindex('_',字段+'_')-1) from 表--放错地方了
--> 测试数据: @s declare @s table (字段 varchar(13)) insert into @s select 'SN0809001_1' union all select 'AB08001_1_1' union all select 'ABC_c' union all select 'SN0809001_2_1' union all select 'dddd' select stuff(字段+'_',charindex('_',字段+'_'),len(字段),'') from @s --或者 select left(字段,charindex('_',字段+'_')-1) from @s --或者 select substring(字段,1,charindex('_',字段+'_')-1) from @s
insert into tb values('SN0809001_1')
insert into tb values('AB08001_1_1')
insert into tb values('ABC_2')
insert into tb values('SN0809001_2_1')
goselect col = left(col , charindex('_',col) - 1) from tb where charindex('_',col) > 0drop table tb/*
col
--------------------
SN0809001
AB08001
ABC
SN0809001(所影响的行数为 4 行)
*/
--> 测试数据: @s
declare @s table (字段 varchar(13))
insert into @s
select 'SN0809001_1' union all
select 'AB08001_1_1' union all
select 'ABC_c' union all
select 'SN0809001_2_1' union all
select 'dddd'
select stuff(字段+'_',charindex('_',字段+'_'),len(字段),'') from @s
--或者
select left(字段,charindex('_',字段+'_')-1) from @s
--或者
select substring(字段,1,charindex('_',字段+'_')-1) from @s