Split values after semi colon to rows

  • PSB

    SSChampion

    Points: 13143

    Hi,

    I need to split column values after ; to new rows . However receiving

    Create table #TestSplit

    ( ID INT, Stops VARCHAR(1000))

    INSERT INTO #TestSplit (ID,Stops)

    SELECT 1,'Procure to Pay x Manage Purchase Orders'

    UNION ALL

    SELECT 1,'Procure to Pay x Manage Purchase Orders;Procure to Pay x Manage Account Payable'

    UNION ALL

    SELECT 1,'Procure to Pay x Process Requisition'

    UNION ALL

    SELECT 1,'Product and Service Delivery x Close Jobs'

    UNION ALL

    SELECT 1,'Product and Service Delivery x Close Jobs;Product and Service Delivery x Execute Jobs;Product and Service Delivery x Prepare Jobs'

    SELECT * FROM #TestSplit

    --Desired results

    SELECT 1,'Procure to Pay x Manage Purchase Orders'

    UNION ALL

    SELECT 1,'Procure to Pay x Manage Purchase Orders'

    UNION ALL

    SELECT 1,'Procure to Pay x Manage Account Payable'

    UNION ALL

    SELECT 1,'Procure to Pay x Process Requisition'

    UNION ALL

    SELECT 1,'Product and Service Delivery x Close Jobs'

    UNION ALL

    SELECT 1,'Product and Service Delivery x Close Jobs'

    UNION ALL

    SELECT 1,'Product and Service Delivery x Execute Jobs'

    UNION ALL

    SELECT 1,'Product and Service Delivery x Prepare Jobs'

    DROP TABLE #TestSplit

    Works fine with sub set of data . But when I use the real table , I get the error?

    Works fine with sub set of data . But when I use the real table , I get the error

     

    XML parsing: line 1, character 10, semicolon expected

     

    SELECT [ID],S.a.value('.', 'VARCHAR(1000)') AS splitVal

    FROM

    (

    SELECT [ID],CAST (N'<H><r>' + Replace(REPLACE([Stops], ';', '</r><r>'),',','</r><r>') + '</r></H>' AS XML) AS [vals]

    FROM dbo.TableName ) d

    CROSS APPLY d.[vals].nodes('/H/r') S(a)

     

  • Phil Parkin

    SSC Guru

    Points: 243862

    I'd suggest that your 'real table' contains a row with a missing semicolon.

    Or is this just a blog post?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • PSB

    SSChampion

    Points: 13143

    I was trying to debug the issue and found out if I exclude this value

    'M&S Management and Distribution x Balance Products and Materials' then I don't get the error . the ampersand is causing the issue . Is there anyway to escape special characters on the fly ?

     

    Thanks,

    PSB

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88174

    You don't need to convert to XML to perform the split:

    Declare @testSplit Table (
    ID int
    , Stops varchar(1000));

    Insert Into @testSplit (ID, Stops)
    Values (1, 'Procure to Pay x Manage Purchase Orders')
    , (1, 'Procure to Pay x Manage Purchase Orders;Procure to Pay x Manage Account Payable')
    , (1, 'Procure to Pay x Process Requisition')
    , (1, 'Product and Service Delivery x Close Jobs')
    , (1, 'Product and Service Delivery x Close Jobs;Product and Service Delivery x Execute Jobs;Product and Service Delivery x Prepare Jobs');

    Select *
    From @testSplit ts
    Cross Apply dbo.DelimitedSplit8K(ts.Stops, ';') ds

    Select *
    From @testSplit ts
    Cross Apply string_split(ts.Stops, ';') x

    You can use the built in string_split in SQL Server 2016 or above if the order doesn't matter - or use the iTVF function dbo.DelimitedSplit8K (https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function) or the updated version here (https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2)

     

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply