如果您关于使用MS SQL Server作为RDBMS,以及skills列除hashtags和si之外不包含其他内容空格,您可以将skills列转换为XML字符串,然后使用SQL Server的内置XML操作函数将此字符串拆分为单独的行。
以下是适用于您在问题中指定的数据示例的方法。
create table people_skills
(
p_id int identity(1, 1) primary key clustered,
name nvarchar(200),
skills nvarchar(1000)
)
go
insert into people_skills (name, skills) values ('Sam', '#IT #communication #administration')
insert into people_skills (name, skills) values ('Alex', '#French #Trainer')
go
select
row_number() over (order by ps.p_id) as ID,
ps.p_id as p_fid,
cast(x.skill_node.query('text()') as nvarchar(100)) as skill
from
(
select
*,
-- Assuming that there are no leading and trailing spaces and that all hashtags are separated by single space.
(cast('' + (replace(replace(skills, '#', ''), ' ', '')) + '' as xml)) skills_xml
from
people_skills
) ps
cross apply
ps.skills_xml.nodes('/skills/skill') as x(skill_node)
如果skills列可以包含不同的主题标签和空格等信息,那么你可能需要转变到skills XML比我上面使用的一个“聪明”的算法。