Split strings by space into individual rows
-
Hi,
String operations are one of my weakest areas in SQL. My requirement is to split strings separated by spaces with different lengths. Products column contains product information for each activity and it can contain one or more than one product in the same row and those are separated by spaces. Records are coming from one of the downstream services and products are stored in such an odd way.
CREATE TABLE Records (
[ID] nvarchar(255),
[Owner Name] nvarchar(255),
[Owner ID] nvarchar(255),
[Territory] nvarchar(255),
[Record Type] nvarchar(255),
[Date] datetime,
[Products] nvarchar(255),
[Status] nvarchar(255)
);INSERT INTO Records ([ID], [Owner Name], [Owner ID], [Territory], [Record Type], [Date], [Products], [Status])
VALUES ('S67753', 'Sales Person 1', 'T865', 'TERR1', 'Call', '21.02.2019 09:30', 'PROD1 PROD2', 'Approved',
'S67751', 'Sales Person 1', 'T865', 'TERR1', 'Call', '21.02.2019 09:00', 'PROD1 PROD3', 'Approved',
'S67748', 'Sales Person 3', 'T1133', 'TERR3', 'Call', '21.02.2019 08:30', 'PROD4 PROD3 PROD5', 'Approved',
'S71935', 'Sales Person 3', 'T1133', 'TERR3', 'Call', '22.02.2019 12:42', 'PROD 5', 'Approved',
'S71931', 'Sales Person 1', 'T865', 'TERR1', 'Call', '22.02.2019 14:03', 'PROD 1', 'Approved'
);What I'm trying to achieve is to split products into individual rows like this;
I've found some string splitters on the web, but couldn't make them work with space delimiters. I would deeply appreciate if someone can point me in the right direction.
Thanks
-
Your sample data is messed up. I removed a bunch of columns so that I had a minimum to deal with. In a word, you need DelimitedSplit8K to do this (Jeff Moden's function... there's an article for it)...
Setup:CREATE TABLE MyData (
[ID] nvarchar(255),
[Products] nvarchar(255),
[Status] nvarchar(255)
);
GO
INSERT INTO MyData ([ID], [Products], [Status])
VALUES ('S67753', 'PROD1 PROD2', 'Approved'),
('S67751', 'PROD1 PROD3', 'Approved'),
('S67748', 'PROD4 PROD3 PROD5', 'Approved'),
('S71935', 'PROD 5', 'Approved'),
('S71931','PROD 1', 'Approved');Solution:
SELECT md.ID
, md.[Status]
, ca.Item
FROM MyData md
CROSS APPLY Utilities.dbo.DelimitedSplit8K(md.Products,' ') ca;The article you need to do this is here
the code is in a link at the bottom of the article.( -
If the problem is the space, you could REPLACE the spaces with an unused character (say '~') before you split the string:
myStringSplitFunction(REPLACE(PRODUCT,' ', '~'), '~') - pietlinden - Tuesday, March 19, 2019 1:32 PM
Nevermind, you updated the post.
-
Yeah... I was having a fit trying to get the formatting etc to work. Does this site not like Edge? (Maybe I should just break down and use Chrome instead?)
-
@pietlinden, @jonathan-2, thank you so much for your help.
I've set up the delimiter and tested it out with the sample data you've shared above. So, it worked like a charm. : ) But, when I've applied this on my real data set it didn't work out as it was supposed to and is appending one more row for spaces into the result set.
I've purposely hidden some values since it is the real data set but hopefully this gives you the idea. For instance, if there are two products in an activity and I run the code it splits products as well as spaces into individual rows and eventually I end up with three rows instead of two.
I feel like I'm missing something important. 🙁
-
Oh... maybe I figured out what Jonathan was talking about...
SELECT id, [status], p2, REPLACE(ca.Item, '_',' ') AS NewItem
FROM
(SELECT id, [status], REPLACE(Products,' ','_') p2
FROM MyData
WHERE Products NOT LIKE '% [^0-9]%'
UNION
SELECT id, [status], Products
FROM MyData
WHERE Products LIKE '% [^0-9]%') md
CROSS APPLY Utilities.dbo.DelimitedSplit8K(md.p2,' ') ca;Note the wonky double replace. (replace space with underscore, then replace underscore with space after split.)
- pietlinden - Tuesday, March 19, 2019 2:18 PM
Currently, this site doesn't like anything when it comes to the forum software. Each browser will have it's own niche of problems with this site. I've tried Chrome, FireFox, IE and they all end up have some sort of problem. Let's hope the new stuff they're going to release for this site in a couple of weeks works better a lot better.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - seismicbeat - Wednesday, March 20, 2019 12:15 AM@pietlinden, @jonathan-2, thank you so much for your help.
I've set up the delimiter and tested it out with the sample data you've shared above. So, it worked like a charm. : ) But, when I've applied this on my real data set it didn't work out as it was supposed to and is appending one more row for spaces into the result set.
I've purposely hidden some values since it is the real data set but hopefully this gives you the idea. For instance, if there are two products in an activity and I run the code it splits products as well as spaces into individual rows and eventually I end up with three rows instead of two.
I feel like I'm missing something important. 🙁
Could it be as simple as adding a WHERE clause?
SELECT md.ID
, md.[Status]
, ca.Item
FROM MyData md
CROSS APPLY Utilities.dbo.DelimitedSplit8K(md.Products,' ') ca
WHERE ca.Item <> ''; -
Luis,
I don't think that will work. That's why I did the weird REPLACE.
The Prod | 5 and Prod | 1 records split to separate records when they should be eliminated. - Luis Cazares - Wednesday, March 20, 2019 9:06 AMseismicbeat - Wednesday, March 20, 2019 12:15 AM@pietlinden, @jonathan-2, thank you so much for your help.
I've set up the delimiter and tested it out with the sample data you've shared above. So, it worked like a charm. : ) But, when I've applied this on my real data set it didn't work out as it was supposed to and is appending one more row for spaces into the result set.
I've purposely hidden some values since it is the real data set but hopefully this gives you the idea. For instance, if there are two products in an activity and I run the code it splits products as well as spaces into individual rows and eventually I end up with three rows instead of two.
I feel like I'm missing something important. 🙁
Could it be as simple as adding a WHERE clause?
SELECT md.ID
, md.[Status]
, ca.Item
FROM MyData md
CROSS APPLY Utilities.dbo.DelimitedSplit8K(md.Products,' ') ca
WHERE ca.Item <> '';@pietlinden, @Luiz, I truly appreciate your help. This did the trick and returned exact values. : )
Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply