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?


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


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
  • I dont see the function ufn_N_Split definition ! – Kin Shah Sep 20 '15 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.