0

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
BEGIN
  SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
  SET @value = SUBSTRING(@Docs, @pos, @len)

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

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

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?

5

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

|improve this answer|||||
-1

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
...
...
...
|improve this answer|||||
  • 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.