SELECT RIGHT(lotno,LEN(lotno)-PATINDEX('%[0-9]%',lotno)) FROM TB
如果只有一个“-”的话 select substring(lotno,charindex('-',lotno),len(lotno)) from tb
if object_id('[tb]') is not null drop table [tb] go create table [tb] (lotno nvarchar(32)) insert into [tb] select 'prc001-1775' union all select 'prd00236-1885' union all select 'prdccaaaa66-1995'select right(lotno,len(lotno)-charindex('-',lotno)) from [tb] /*-------------------------------- 1775 1885 1995(3 個資料列受到影響)*/
--> 测试数据:@table declare @table table([lotno] varchar(16)) insert @table select 'prc001-1775' union all select 'prd00236-1885' union all select 'prdccaaaa66-1995'select stuff([lotno],1,charindex('-',[lotno]),'') as [lotno] from @table ------------------ 1775 1885 1995
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([col] varchar(16)) insert [TB] select 'lotno' union all select 'prc001-1775' union all select 'prd00236-1885' union all select 'prdccaaaa66-1995'select right(col,len(case when charindex('-',col)>0 then col else '-'+col end)-charindex('-',case when charindex('-',col)>0 then col else '-'+col end)) from [TB]/*
create table [tb] (lotno nvarchar(32)) insert into [tb] select 'prc001-1775' union all select 'prd00236-1885' union all select 'prdccaaaa66-1995'select substring(lotno , charindex('-',lotno) + 1 , len(lotno)) from tb where charindex('-',lotno) > 0drop table tb/*
lotno 是字段名啊?NND --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([lotno] varchar(16)) insert [TB] select 'prc001-1775' union all select 'prd00236-1885' union all select 'prdccaaaa66-1995'select right(lotno,len(lotno)-charindex('-',lotno)) from [TB]/*
---------------- 1775 1885 1995(所影响的行数为 3 行)*/ drop table TB
select substring(lotno,charindex('-',lotno),len(lotno))
from tb
go
create table [tb] (lotno nvarchar(32))
insert into [tb]
select 'prc001-1775' union all
select 'prd00236-1885' union all
select 'prdccaaaa66-1995'select right(lotno,len(lotno)-charindex('-',lotno)) from [tb]
/*--------------------------------
1775
1885
1995(3 個資料列受到影響)*/
declare @table table([lotno] varchar(16))
insert @table
select 'prc001-1775' union all
select 'prd00236-1885' union all
select 'prdccaaaa66-1995'select stuff([lotno],1,charindex('-',[lotno]),'') as [lotno] from @table
------------------
1775
1885
1995
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([col] varchar(16))
insert [TB]
select 'lotno' union all
select 'prc001-1775' union all
select 'prd00236-1885' union all
select 'prdccaaaa66-1995'select right(col,len(case when charindex('-',col)>0 then col else '-'+col end)-charindex('-',case when charindex('-',col)>0 then col else '-'+col end)) from [TB]/*
----------------
lotno
1775
1885
1995(所影响的行数为 4 行)
*/
drop table TB
insert into [tb]
select 'prc001-1775' union all
select 'prd00236-1885' union all
select 'prdccaaaa66-1995'select substring(lotno , charindex('-',lotno) + 1 , len(lotno)) from tb where charindex('-',lotno) > 0drop table tb/*
--------------------------------
1775
1885
1995(所影响的行数为 3 行)
*/
是字段名啊?NND
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([lotno] varchar(16))
insert [TB]
select 'prc001-1775' union all
select 'prd00236-1885' union all
select 'prdccaaaa66-1995'select right(lotno,len(lotno)-charindex('-',lotno)) from [TB]/*
----------------
1775
1885
1995(所影响的行数为 3 行)*/
drop table TB