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 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?
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.
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 ... ... ...
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂