将字符串拆分为列中的单词 [英] Split string into words in columns

问题描述

我希望在 SQL Server 2014 的列中将字符串拆分为单词.我找到了一些解决方案，但所有解决方案都在行中给出结果.如何将下面的字符串分成几列?

I am looking to split a string into words in columns in SQL Server 2014. I have found a few solutions but all of them are giving the results in rows. How can I break the below string into columns?

一二三四五"

推荐答案

您可以使用 SQL 拆分字符串函数 将字符串拆分成单词，并使用单词在原始字符串中的顺序，可以使用 CASE 语句，如PIVOT 查询并显示为列

You can use a SQL split string function to seperate the string into words and using the order of the word in the original string, you can use CASE statements like a PIVOT query and display as columns

这是一个示例

declare @string varchar(max) = 'First Second Third Fourth Fifth'

;with cte as (
select
    case when id = 1 then val end as Col1,
    case when id = 2 then val end as Col2,
    case when id = 3 then val end as Col3,
    case when id = 4 then val end as Col4,
    case when id = 5 then val end as Col5
from dbo.split( @string,' ')
)
select
    max(Col1) as Col1,
    max(Col2) as Col2,
    max(Col3) as Col3,
    max(Col4) as Col4,
    max(Col5) as Col5
from cte

如果你不能创建UDF，你可以在你的SQL代码中使用如下逻辑

If you cannot create a UDF, you can use the logic in your SQL code as follows

请注意，如果您的数据在数据库表列中，您可以简单地替换第一个 SQL CTE 表达式

Please note that if you have your data in a database table column, you can simply replace column content in the first SQL CTE expression

declare @string varchar(max) = 'First Second Third Fourth Fifth'

;with cte1 as (
    select convert(xml, N'<root><r>' + replace(@string,' ','</r><r>') + '</r></root>') as rawdata
), cte2 as (
  select
    ROW_NUMBER() over (order by getdate()) as id,
    r.value('.','varchar(max)') as val
  from cte1
  cross apply rawdata.nodes('//root/r') as records(r)
)
select
    max(Col1) as Col1,
    max(Col2) as Col2,
    max(Col3) as Col3,
    max(Col4) as Col4,
    max(Col5) as Col5
from (
    select
        case when id = 1 then val end as Col1,
        case when id = 2 then val end as Col2,
        case when id = 3 then val end as Col3,
        case when id = 4 then val end as Col4,
        case when id = 5 then val end as Col5
    from cte2
) t

这篇关于将字符串拆分为列中的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！