Split values after semi colon to rows

Question

Post reply

Split values after semi colon to rows

PSB

SSChampion

Points: 13195

June 19, 2019 at 3:01 pm

#3655304

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)

This topic was modified 5 years, 6 months ago by Steve Jones - SSC Editor. Reason: Formatted code

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

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

Phil Parkin SSC Guru Points: 246976 More actions · Answer 1

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

Or is this just a blog post?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 2

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 SSC Guru Points: 90304 More actions · Answer 3

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)

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs