时间戳

首页 > 解决方案 > sql split row value before and after substring

sql-server - sql split row value before and after substring

问题描述

标签： sql-server

解决方案

No need for ordinal splitter. It's a simple unpivot using CROSS APPLY

[EDIT] Changed method of splitting to look for '%/%/%' in cases where there's an alias

select cast(unpvt.id as varchar(9))+iif(unpvt.seq=1, '', '.1') ID,
       trim(replace(replace(replace(unpvt.[Name],'f/k/a - ',''),'f/k/a ',''),'n/k/a ','')) [Name]
from (values (1, 'Evil Empire, f/k/a - Starbucks'),
             (2, 'Aubrey Drake Graham, n/k/a Drake'),
             (3, 'Thomas Johnson Bridge, f/k/a Solomans Bridge'),
             (4, 'Thomas, J, Cat, f/k/a Solomans,,,Bridge'),
             (5, 'Thomas')) v(id, [Name])
     cross apply (values (v.id, substring(v.[Name], 1, patindex('%/%/%', v.Name)-4), 1),
                         (v.id, substring(v.[Name], patindex('%/%/%', v.Name)-1, len(v.[Name])), 2)) unpvt(id, [Name], seq)
where patindex('%/%/%', v.[Name])>1;

Results

ID  Name
1   Evil Empire
1.1 Starbucks
2   Aubrey Drake Graham
2.1 Drake
3   Thomas Johnson Bridge
3.1 Solomans Bridge
4   Thomas, J, Cat
4.1 Solomans,,,Bridge

推荐阅读