update biao set 字段=case when len(字段)>XXX then repalce() else 字段
create table test_t (col varchar(100)) insert test_t select '123456789012345678/姓名:' union select '123456789012345678/姓名:小王' update test_t set col=LEFT(col,charindex('/',col)-1) select * from test_t /* col ---------------------------------------------------------------------------------------------------- 123456789012345678 123456789012345678 */
n关键是你想怎么办 update test_t set col=LEFT(col,charindex('/',col)-1) where charindex('/',col)>0
if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(28)) insert [tb] select '123456789012345678/姓名:' union all select '123456789012345678/姓名:小王'
update tb set col=replace(col,'/姓名:','') where charindex(':',reverse(rtrim(col)))=1select * from tb/** col ---------------------------- 123456789012345678 123456789012345678/姓名:小王(2 行受影响) **/
我刚想了一下,是不是这样可以,如:where col like '%/姓名:'
create table test_t (col varchar(100)) insert test_t select '123456789012345678/姓名:' union select '123456789012345678/姓名:小王' update test_t set col=LEFT(col,charindex('/',col)-1) where len(STUFF(col,1,charindex('/姓名:',col)+3,''))=0 select * from test_t /* col ---------------------------------------------------------------------------------------------------- 123456789012345678 123456789012345678/姓名:小王*/ drop table test_t
set 字段=case when len(字段)>XXX then repalce() else 字段
(col varchar(100))
insert test_t select '123456789012345678/姓名:' union select '123456789012345678/姓名:小王'
update test_t
set col=LEFT(col,charindex('/',col)-1)
select * from test_t
/*
col
----------------------------------------------------------------------------------------------------
123456789012345678
123456789012345678
*/
update test_t
set col=LEFT(col,charindex('/',col)-1)
where charindex('/',col)>0
go
create table [tb]([col] varchar(28))
insert [tb]
select '123456789012345678/姓名:' union all
select '123456789012345678/姓名:小王'
update tb
set col=replace(col,'/姓名:','')
where charindex(':',reverse(rtrim(col)))=1select * from tb/**
col
----------------------------
123456789012345678
123456789012345678/姓名:小王(2 行受影响)
**/
(col varchar(100))
insert test_t select '123456789012345678/姓名:' union select '123456789012345678/姓名:小王'
update test_t
set col=LEFT(col,charindex('/',col)-1)
where len(STUFF(col,1,charindex('/姓名:',col)+3,''))=0
select * from test_t
/*
col
----------------------------------------------------------------------------------------------------
123456789012345678
123456789012345678/姓名:小王*/
drop table test_t