How to split a string into multiple columns based on on type of delimiter repeated multiple times?

Question

I figured out how to split a string based on delimiters (in this case spaces).

select parsename(replace(replace(replace([Column 0],'  ',' '),'  ',' '),' ','.'), 4) [Date],
parsename(replace(replace(replace([Column 0],'  ',' '),'  ',' '),' ','.'), 3) ID,
parsename(replace(replace(replace([Column 0],'  ',' '),'  ',' '),' ','.'), 2)  Rank1,  
parsename(replace(replace(replace([Column 0],'  ',' '),'  ',' '),' ','.'), 1)   Rank2

--,replace(replace(replace(strCol,'  ',' '),'  ',' '),' ','.') 
from AllData

Before.

After.

The thing is, I need to split strings, based on 1 to 7 delimiters, and I think the code above will become too hard to maintain at some point. Is there a more elegant script that can help me achieve what I want to do? Maybe a Table Value Function would be better. I'm not good at creating those things.

Thanks to all.

I am running SQL Server 2008.

Drop Table
[Raw_Data_ParsedIDs]

SELECT  DISTINCT
        split.a.value ('/A[1]', 'VARCHAR(MAX)') [Piece1],
          split.a.value ('/A[2]', 'VARCHAR(MAX)') [Piece2],
          split.a.value ('/A[3]', 'VARCHAR(MAX)') [Piece3],
          split.a.value ('/A[4]', 'VARCHAR(MAX)') [Piece4],
          split.a.value ('/A[5]', 'VARCHAR(MAX)') [Piece5],  
          split.a.value ('/A[6]', 'VARCHAR(MAX)') [Piece6],  
          split.a.value ('/A[7]', 'VARCHAR(MAX)') [Piece7]
INTO [Raw_Data_ParsedIDs] 
FROM 
(
SELECT      CAST('<A>' + REPLACE(SrcID, '|', '</A><A>') + '</A>' AS XML) AS Data
FROM  dbo.RAW_DATA_HIST
) a cross apply Data.nodes('/A') AS split(a)

Will you be able to know which delimiter is in the string? If you know it, you can create a function and pass the string and delimiter and get the output from the function.Creating the function will make the task easier. — Jineesh Uvantavida
– Jineesh Uvantavida, Commented Nov 8, 2017 at 13:35

Yogesh Sharma · Accepted Answer · 2017-11-09 04:17:03Z

1

You could use XML Method for split the strings :

select    DISTINCT
          split.a.value ('/A[1]', 'VARCHAR(MAX)') [DATA],
          split.a.value ('/A[2]', 'VARCHAR(MAX)') [ID],
          split.a.value ('/A[3]', 'VARCHAR(MAX)') [RANK1],
          split.a.value ('/A[4]', 'VARCHAR(MAX)') [RANK2] from 
(
    SELECT CAST('<A>'+REPLACE(<column>, ' ', '</A><A>')+'</A>' AS XML) AS Data from <table_name>
) a cross apply Data.nodes('/A') AS split(a)

However, if you want to insert the parsed data then follow the syntax :-

INSERT INTO <table_name>
SELECT    DISTINCT
          split.a.value ('/A[1]', 'VARCHAR(MAX)') [Piece1],
          split.a.value ('/A[2]', 'VARCHAR(MAX)') [Piece2],
          split.a.value ('/A[3]', 'VARCHAR(MAX)') [Piece3],
          split.a.value ('/A[4]', 'VARCHAR(MAX)') [Piece4],
          split.a.value ('/A[5]', 'VARCHAR(MAX)') [Piece5],  
          split.a.value ('/A[6]', 'VARCHAR(MAX)') [Piece6],  
          split.a.value ('/A[7]', 'VARCHAR(MAX)') [Piece7]
FROM 
(
SELECT      CAST('<A>' + REPLACE(SrcID, '|', '</A><A>') + '</A>' AS XML) AS Data
FROM  dbo.RAW_DATA_HIST
) a cross apply Data.nodes('/A') AS split(a)

edited Nov 9, 2017 at 4:17

answered Nov 8, 2017 at 12:47

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ASH Over a year ago

That works perfect to parse out all the fields. How can I include the other fields in that table as well? For instance, I have [Currency], [Product], etc. How can I include those, along with the parsed out fields? Basically, I want to clean the filed you gave me, and combine the cleaned field with other fields, in a NEW TABLE. Thank you.

ASH Over a year ago

Yogesh, I modified my original post to show the code I am testing (based on what you posted earlier). Everything is jammed into the first field. How can I parse the data into 7 fields, in a new table, and include the fields from the original table?

ASH Over a year ago

Ok. Got it. Thanks a bunch!!

Collectives™ on Stack Overflow

How to split a string into multiple columns based on on type of delimiter repeated multiple times?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related