将 SQL 列中的分隔值拆分为使用多个分隔符的多行 [英] Splitting delimited values in a SQL column into multiple rows that uses multiple delimiters

问题描述

我正在寻找一些帮助来拆分在 sql 函数/语句中使用多个分隔符的字段.我遵循了一些已经列出的例子这里

I'm looking for a little help in splitting apart a field that uses multiple delimiters in a sql function/ statement. I've followed a few examples already listed HERE

在这里使用单个分隔符可以很好地工作.但是，我需要拆分基于以下内容的字段.它的格式将是 qty x itemno description、qty x itemno description 等.因此，我需要将第一个x"、第二个空格"和最后一个逗号上的语句分开.我想我可以根据正则表达式语句，但不确定如何去做..

here that work very well with a single delimiter. However I need to split apart a field that is based on the following. its format would be qty x itemno description, qty x itemno description, etc. I therefore need to break apart the statement on the first ' x' the second space ' ' and the final comma.. I figure I can split this based on a regular expression statement but unsure about how to go about it..

有人可以帮忙吗?

    OnSameLine = substring( ( SELECT ', ' + 
                 coalesce (CAST(CNT.qty AS varchar(1000)), '') +                  
                 ' x ' + '['+ coalesce (CNT.Itemno,'') + ']' + 
                 ' ' + coalesce (STK.Desc#1,'')

我需要的格式..

PK qty itemno description
PK qty itemno description 

等等.

推荐答案

试试这个:

基于这里

;with T(id, Cols) as
(
  select 1, '10 x 1234 description1,11 x 456 description2' 
  UNION ALL
  select 2, '40 x 123478 description3,51 x 45678 description4' 
),
--first split all comma separated into different rows
CTE2 AS 
(
    SELECT id, 
    CAST(N'<H><r>' + replace(Replace(Vals.a.value('.', 'NVARCHAR(50)'),
        ' ','|'), '|', '</r><r>') + '</r></H>' as XML) Cols
    FROM
    (
    SELECT *,CAST (N'<H><r>' + Replace(cols,',','</r><r>') + 
        '</r></H>' AS XML) AS [vals]
    FROM T) d
    CROSS APPLY d.[vals].nodes('/H/r') Vals(a)
)
-- split all ' ' demilited values now
SELECT distinct id, Vals.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Qty,
Vals.a.value('(/H/r)[2]', 'VARCHAR(100)') AS X,
Vals.a.value('(/H/r)[3]', 'VARCHAR(100)') AS ItemNo,
Vals.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Description
FROM
(
SELECT *
FROM CTE2) d
CROSS APPLY d.[cols].nodes('/H/r') Vals(a)

这篇关于将 SQL 列中的分隔值拆分为使用多个分隔符的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！