update tb set a=substring(col,len(col)-charindex('-',reverse(col))+1,len(col))
DECLARE @TB TABLE([COL] VARCHAR(19)) INSERT @TB SELECT '[1]-[2]-[3]-[4]-[5]' UNION ALL SELECT '[2]-[5]-[1]'UPDATE @TB SET COL=LEFT(COL, LEN(COL)-CHARINDEX('-', REVERSE(COL)))SELECT * FROM @TB /* COL ------------------- [1]-[2]-[3]-[4] [2]-[5]*/
---测试数据 if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(19)) insert [tb] select '[1]-[2]-[3]-[4]-[5]' union all select '[2]-[5]-[1]'
---更新 update tb set col=left(col,len(col)-charindex('-',reverse(col)))---查询 select * from [tb]---结果 col ------------------- [1]-[2]-[3]-[4] [2]-[5](所影响的行数为 2 行)
--> 测试时间:2009-07-09 12:02:11 --> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab] create table [tab]([s] varchar(19)) insert [tab] select '[1]-[2]-[3]-[4]-[5]' union all select '[2]-[5]-[1]'select S=left(s,len(s)-charindex('-',REVERSE(s))) from [tab] /* S ------------------- [1]-[2]-[3]-[4] [2]-[5](所影响的行数为 2 行)*/ drop table tab
declare @tb table(col varchar(100)) insert @tb select '[1]-[2]-[3]-[4]-[5]' insert @tb select '[2]-[5]-[1]' update @tb set col=left(col,len(col)-charindex('-',reverse(col))) select * from @tb /* col ---------------------------------------------------------------------------------------------------- [1]-[2]-[3]-[4] [2]-[5](所影响的行数为 2 行) */
update tb set col=reverse(substring(reverse(col),charindex('-',reverse(col))+1,len(col)))
set @s='[1]-[2]-[3]-[4]-[5]'
select left(@s,len(@s)-charindex('-',REVERSE(@s)))
/*
--------------------------------------------------------------------------------
[1]-[2]-[3]-[4](所影响的行数为 1 行)*/
INSERT @TB
SELECT '[1]-[2]-[3]-[4]-[5]' UNION ALL
SELECT '[2]-[5]-[1]'UPDATE @TB SET COL=LEFT(COL, LEN(COL)-CHARINDEX('-', REVERSE(COL)))SELECT *
FROM @TB
/*
COL
-------------------
[1]-[2]-[3]-[4]
[2]-[5]*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(19))
insert [tb]
select '[1]-[2]-[3]-[4]-[5]' union all
select '[2]-[5]-[1]'
---更新
update tb
set col=left(col,len(col)-charindex('-',reverse(col)))---查询
select * from [tb]---结果
col
-------------------
[1]-[2]-[3]-[4]
[2]-[5](所影响的行数为 2 行)
--> 测试时间:2009-07-09 12:02:11
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([s] varchar(19))
insert [tab]
select '[1]-[2]-[3]-[4]-[5]' union all
select '[2]-[5]-[1]'select S=left(s,len(s)-charindex('-',REVERSE(s))) from [tab]
/*
S
-------------------
[1]-[2]-[3]-[4]
[2]-[5](所影响的行数为 2 行)*/
drop table tab
insert @tb select '[1]-[2]-[3]-[4]-[5]'
insert @tb select '[2]-[5]-[1]' update @tb set col=left(col,len(col)-charindex('-',reverse(col)))
select * from @tb
/*
col
----------------------------------------------------------------------------------------------------
[1]-[2]-[3]-[4]
[2]-[5](所影响的行数为 2 行)
*/
例子:
[1]-[2]-[3]-[4]-[5]
[2]-[5]-[1]
[2]现在要删除最后一个 "-"后的字符,包括最后一个“-”;同时如果字段里没有“-”,就置为null。
希望删除后的效果是
[1]-[2]-[3]-[4]
[2]-[5]
null请问怎么做呀?