SELECT REVERSE(SUBSTRING(REVERSE(COL),1,CHARINDEX('-',REVERSE(COL))-1)) FROM TB
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T go create table #T (col varchar(27)) insert into #T select '200951985529182-04-2-4-97' union all select '2009519855291823-04-2-4-197' union all select '2009519855291821-04-2-4-9' select REVERSE(stuff(REVERSE(col),charindex('-',REVERSE(col)),LEN(col),'')) from #T/* 97 197 9 */
declare @s varchar(100)
set @s = '2009519855291823-04-2-4-197'
select reverse(substring(reverse(@s),1,charindex('-',reverse(@s))-1))
/*
197
*/
declare @t table(col varchar(50))
insert @t select '200951985529182-04-2-4-97'
insert @t select '2009519855291823-04-2-4-197'
insert @t select '2009519855291821-04-2-4-9'
select right(col,len(col)-charindex('-',col))from @t
/*--------------------------------------------------
04-2-4-97
04-2-4-197
04-2-4-9(影響 3 個資料列)*/
if object_id('tempdb.dbo.#T') is not null drop table #T
go
create table #T (col varchar(27))
insert into #T
select '200951985529182-04-2-4-97' union all
select '2009519855291823-04-2-4-197' union all
select '2009519855291821-04-2-4-9'
select
REVERSE(stuff(REVERSE(col),charindex('-',REVERSE(col)),LEN(col),''))
from #T/*
97
197
9
*/
declare @t table(col varchar(50))
insert @t select '200951985529182-04-2-4-97'
insert @t select '2009519855291823-04-2-4-197'
insert @t select '2009519855291821-04-2-4-9'
select right(col,charindex('-',reverse(col))-1)from @t
/*--------------------------------------------------
97
197
9(影響 3 個資料列)*/
set @s = '2009519855291823-04-2-4-197'
select reverse(substring(reverse(@s),1,charindex('-',reverse(@s))-1))
/* 197 */
用这个Reverse函数哦, 返回字符表达式的逆向表达式
DECLARE @T TABLE(aa VARCHAR(200))
INSERT @T SELECT '200951985529182-04-2-4-97 '
INSERT @T SELECT '2009519855291823-04-2-4-197 '
INSERT @T SELECT '2009519855291821-04-2-4-9 'SELECT REVERSE(left(REVERSE(aa),CHARINDEX('-',REVERSE(aa))-1))
FROM @T
set @s = '2009519855291823-04-2-4-197'select right(@s,charindex('-',reverse(@s))-1)
/*
197
*/
set @t='200951985529182-04-2-4-97'
select right(@t,charindex('-',reverse(@t))-1)