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?
Best Answer
You should make use of a split string function.
Aaron Bertrand blogged about some of the alternatives you have in Split strings the right way – or the next best way