I have a table that has one column that has multiple values.
I need to create a new table with each value in a column and I am not sure how.
create table #mytable
(
ID NVARCHAR(50),
MainImage nvarchar(MAX)
)
INSERT INTO #mytable (ID, MainImage)
VALUES('asdf123', '["0535bd68e0b14c0f6ff78dff12a7da56e81bd5d0", "27d8b9e313e5c50a650168bea38052cd49fc48b2","cbdfd4438d8259baacad4db3f66d7178c0648f38", "b53afe5d5471003d90e09906f08c0b0fc43004b4","53c6a621ec464a8d174e8094e883bddb5db7c795"]'),
('asdf144', '03e9c0f463de2d92db2fe89790cf8c80d616b825'),
('asthd23', '["b82ae6fa21714e0a3885bdd78195b94fbabd5d65","b66347b4c5436bd69aa427bbbe59d566e024389f"]');
select * from #mytable
my end result should look like
create table #mytableNEW
(
ID NVARCHAR(50),
MainImage nvarchar(MAX)
)
INSERT INTO #mytableNEW (ID, MainImage)
VALUES('asdf123', '0535bd68e0b14c0f6ff78dff12a7da56e81bd5d0'),
('asdf123', '27d8b9e313e5c50a650168bea38052cd49fc48b2'),
('asdf123', 'cbdfd4438d8259baacad4db3f66d7178c0648f38'),
('asdf123', 'b53afe5d5471003d90e09906f08c0b0fc43004b4'),
('asdf123', '53c6a621ec464a8d174e8094e883bddb5db7c795'),
('asdf144', '03e9c0f463de2d92db2fe89790cf8c80d616b825'),
('asthd23', 'b82ae6fa21714e0a3885bdd78195b94fbabd5d65'),
('asthd23', 'b66347b4c5436bd69aa427bbbe59d566e024389f');
select * from #mytableNEW
I am just not sure how to make the end result.
thanks
astrid
November 20, 2019 at 3:49 pm
Is the number of entries consistent? Or could there be 2, 3, 4, or more in the brackets?
Look at PATINDEX() and CHARINDEX() with SUBSTRING here. What you really want to do is split this string, which is something that you can also get from this article: https://qa.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Try running this on your sample data ... it performs the split for you:
SELECT m.ID
,REPLACE(REPLACE(REPLACE(ss.value, '"', ''), '[', ''), ']', '')
FROM #mytable m
CROSS APPLY STRING_SPLIT(m.MainImage, ',') ss;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 20, 2019 at 3:56 pm
Thanks to you both, the number was not consistent and that is why I was not sure how to split it.
I need to get stronger on using cross apply.
thanks again. I will be ticking Phil's answer to fit all my code. it did work on my test sample data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply