Dividing a string column

Question

Post reply

Dividing a string column

astrid 69000

Hall of Fame

Points: 3208

November 20, 2019 at 3:36 pm

Go to Answer

#3699234

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

This topic was modified 5 years, 1 month ago by Steve Jones - SSC Editor.

Phil Parkin

SSC Guru

Points: 246987
More actions
November 20, 2019 at 3:51 pm
Answer
#3699240
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;
```
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

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://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

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.