这里分隔符号是 ‘-’,我只是想提取字符串里面的第一组数字和最后一组数字,字符串里面分割符号的数量不定,有可能3个,也有可能很多个原始数据:
id name
1 12-32A-DDD
2 32-22F-FDD-SD
3 1-32K-F-S-DD-Q-10希望的输出结果是:id name name2
1 12 DDD
2 32 SD
3 1 10
id name
1 12-32A-DDD
2 32-22F-FDD-SD
3 1-32K-F-S-DD-Q-10希望的输出结果是:id name name2
1 12 DDD
2 32 SD
3 1 10
name2=REVERSE(left(REVERSE(name),len(name)-charindex('-',REVERSE(name))))
from tb
id,
name=left(name,len(name)-charindex('-',name)),
name2=REVERSE(left(REVERSE(name),len(name)-charindex('-',REVERSE(name))))
from
tb
declare @t table(
id int
,name varchar(30)
);
insert into @t
SELECT 1, '12-32A-DDD' UNION ALL
select 2, '32-22F-FDD-SD' UNION ALL
select 3, '1-32K-F-S-DD-Q-10'
;WITH t1 AS
(
SELECT ID, Name+'-' as name
FROM @t
),
t2 as
(
SELECT ID, CONVERT(VARCHAR(30),LEFT(name,CHARINDEX('-',name)-1)) AS Subname
,STUFF(name, 1, CHARINDEX('-',name), '') AS name, 1 AS Level
FROM t1
UNION ALL
SELECT t1.ID, CONVERT(VARCHAR(30),LEFT(t2.name,CHARINDEX('-',t2.name)-1))
,STUFF(t2.name, 1, CHARINDEX('-',t2.name), ''), t2.Level+1
FROM t2 JOIN
t1 ON t2.ID=t1.ID and t2.name>''
)
SELECT ID
, MIN(CASE WHEN name>'' then subname else Null end) As name
, MIN(CASE WHEN name='' then subname else Null end) As name2
FROM t2
where level=1 or name=''
group by ID
/*
(3 行受影响)
ID name name2
----------- ------------------------------ ------------------------------
1 12 DDD
2 32 SD
3 1 10
警告: 聚合或其他 SET 操作消除了空值。
*/
create table #tb
(id int,name nvarchar(400))
insert #tb
select 1, '12-32A-DDD' union all
select 2, '32-22F-FDD-SD' union all
select 3, '1-32K-F-S-DD-Q-10'select id,left(name,charindex('-',name)-1) as name1,
reverse(left(reverse(name),charindex('-',reverse(name))-1)) as name2
from #tb