27,580
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](col varchar(20))
insert [tb] select '部门-1011-财务科'
insert [tb] select '部门-1022'
select left(substring(col,patindex('%[0-9]%',col),len(col+'-')-patindex('%[0-9]%',col)),charindex('-',substring(col,patindex('%[0-9]%',col),len(col+'-')-patindex('%[0-9]%',col))+'-')-1) as col from tb
/*
col
--------------------
1011
1022
(2 行受影响)
*/
DECLARE @TB TABLE([col] NVARCHAR(7))
INSERT @TB
SELECT N'部门-1011' UNION ALL
SELECT N'部门-1022'
SELECT RIGHT(col,4) as col
FROM @TB
/*
col
----
1011
1022
*/
SELECT RIGHT(RTRIM(@STR),4) FROM TB
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](col varchar(20))
insert [tb] select '部门-1011'
insert [tb] select '部门-1022'
select right(col,len(col)-charindex('-',col)) as col from tb
/*
col
--------------------
1011
1022
(2 行受影响)
*/
declare @STR varchar(50)
set @STR=N'部门-1011'
select right(@STR,charindex('-',@STR)+1)
select 姓名=left(str,patindex('%[0-9]%',str+'0')-1)
,证件号=stuff(str,1,patindex('%[0-9]%',str+'0')-1,'')
from( -- 这后面是直接写测试数据,你可以直接写表名
select str=N'张三612345678901234' union all
select str=N'张三三612345678901234' union all
select str=N'李四612345678901234567' union all
select str=N'李四四612345678901234567' union all
select str=N'王五(护照号)12345678' union all
select str=N'王五五(护照号)12345678'
)a
/*
张三 612345678901234
张三三 612345678901234
李四 612345678901234567
李四四 612345678901234567
王五(护照号) 12345678
王五五(护照号) 12345678
*/
select right(col,len(col)-charindex('-',col)) as col from tb
select right(col,5) from tb