Single query approach to split and copy string values in a column

All we need is an easy explanation of the problem, so here it is.

This is a part of my previous question.

I have the below logic to split a string value from a column in table tblProjects as below:

SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID 
SET @Docs = @Docs + '$'
SET @pos = 0
SET @len = 0

WHILE CHARINDEX('$', @Docs, @pos + 1)>0
  SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
  SET @value = SUBSTRING(@Docs, @pos, @len)

  IF CHARINDEX('|', @value) >0
    SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
            @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
    INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)

  SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1

A sample input would be

insert into tblProjects(ID, Documents) 
values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');

I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.

Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You should make use of a split string function.

select left(S.Item, charindex('|', S.Item) - 1),
       stuff(S.Item, 1, charindex('|', S.Item), '')
from dbo.tblProjects as P
  cross apply dbo.SplitString(P.Documents, '$') as S;

Aaron Bertrand blogged about some of the alternatives you have in Split strings the right way – or the next best way

Method 2

The following example works perfectly fine with this single query –

Here is the data example

Table = CityItem

ID     Name     City       Items
===    ===      =====      ======
1      Michael  Miami      item|item2|item3|item4|item5
2      Jorge    Hallandale item|item2|item3|item4|item5

SQL Query for the result given below –

SELECT ID, Name, City, s.value AS Item
FROM CityItem as mb
  CROSS APPLY dbo.ufn_N_Split('|', mb.Items) as s;

NOTE:- “ufn_N_Split” is a custom function that splits string values (like items) into a column named “value”.

Output / Result

ID     Name     City       Item
===    =====    =====      =======
1      Michael  Miami      item
1      Michael  Miami      item2
1      Michael  Miami      item3
1      Michael  Miami      item4
1      Michael  Miami      item5
2      Jorge    Hallandale item
2      Jorge    Hallandale item2
2      Jorge    Hallandale item3
2      Jorge    Hallandale item4
2      Jorge    Hallandale item5

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply