September 18, 2024 at 1:34 pm
My table1 has a column that has the following in it: (2)Joystick,(3)Candies,(6)Wires
I want to write to a temp table with the ColumnNames: ID, description, quantity so it looks like this:
KeyId Joystick 2
KeyId Candies 3
KeyId Wires 6
So, the one row write out three rows to the temp table. But, the single row may have one item, or several. One would look like (7)Joystick
How do I write in SQL to split a concatenated column into several rows into a temp table?
thank you , JP
September 18, 2024 at 1:46 pm
did you check with this soluiton ? "Tally OH! An Improved SQL 8K “CSV Splitter” Function"
And its followup comments
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 18, 2024 at 2:42 pm
The STRING_SPLIT() function was introduced in SQL 2016. It's not as powerful as the solution Johan is pointing to, but it should suffice for your purposes.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 18, 2024 at 4:15 pm
Like this?
drop table if exists #RawInput;
go
drop table if exists #FinalOutput;
go
create table #RawInput (
Whatever varchar(500) not null
);
create table #FinalOutput (
ID int not null identity(1,1) primary key,
"description" varchar(40),
quantity int
);
insert into #RawInput (Whatever)
values
('(2)Joystick,(3)Candies,(6)Wires'),
('(7)Joystick');
with
SplitData as (
select cast(value as varchar(50)) as WhateverSplit
from #RawInput i
cross apply string_split(i.Whatever,',')
),
SplitDataExtractedStrings as (
select
substring(WhateverSplit,
charindex('(' ,WhateverSplit) + 1, -- Calculate where the numeric value starts, i.e. one character past the ( marker.
charindex(')', WhateverSplit) - charindex('(', WhateverSplit) - 1 -- Calculate how long the value that we must extract is, ie. the value between the brackets.
) as quantity_str,
substring(WhateverSplit,
charindex(')', WhateverSplit) + 1, -- Calculate where the "description" starts, i.e. one character past the ) marker.
50 -- length value that is "big enough" to take all the rest of the string
) as "description"
from SplitData
)
insert into #FinalOutput ("description",quantity)
select
"description",
try_cast(quantity_str as int)
from SplitDataExtractedStrings;
/* Let's check what is in the output table */select * from #FinalOutput;
September 19, 2024 at 9:11 pm
The short answer is to look up the STRING_SPLIT() function. Microsoft added it to let bad programmers kludge their code. Cleaning up data until at least you get to1NF should be done automatically. You don't seem to want to follow the netiquette that has been in place for over 40 years now, but your table should look something like this:
CREATE TABLE Receipts
(receipt_nbr CHAR(10) NOT NULL PRIMARY KEY,
item_name VARCHAR(20) NOT NULL,
receipt_qty INTEGER NOT NULL,
UNIQUE (receipt_nbr, item_name) );
This way, an item will appear only once in each receipt. What you've done is mimic a 1950s punchcard file system which uses scratch tapes. In SQL, we prefer doing things in one statement, not in step-by-step procedural calls with scratch tapes. Try writing an INSERT INTO statement which build your final normalized table from the un-normalized, raw data that you seem to have. I would then also beat up the guy that handed you data in this format.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 20, 2024 at 2:03 am
Thank you. I'll give it a try.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply