Hi,
Below is the sample data
Declare @text = '1,1000,FirstName1,LastName1|2,1001,FirstName2,LastName2|3,1002,FirstName3,LastName3|4,1003,'',LastName4
|5,1004,FirstName5,'''
Destination table structure :
Create table Emp_Temp(Id int, IdProduct int,FirstName varchar(200), lastname varchar(200));
Expected table values after parsing the delimiters.
insert into Emp_Temp(id,IdProduct,FirstName,lastname)
select 1, 1000,'FirstName1','LastName1' union all
select 2, 1001,'FirstName2','LastName2' union all
select 3, 1002,'FirstName3','LastName3' union all
select 4, 1003,'','LastName4' union all
select 5, 1004,'FirstName5',''
I am using delimitedsplit8k function created by jeff. i can split the '|'. after that i have to iterate the splitted values and split ','. would it be possible to do this requirement without the loop? sometimes, we get firstname or lastname as empty
Any sample query to achieve this would be highly helpful
January 30, 2020 at 3:35 pm
do y0u have to use a delimited string?
if it's a proc that's doing this then can you pass a table value parameter in rather than a delimited string?
MVDBA
January 30, 2020 at 3:36 pm
Something like this?! It's only a start, obviously.
SELECT DS2.*
FROM dbo.DelimitedSplit8K(@text, '|') DS1
CROSS APPLY dbo.DelimitedSplit8K(DS1.Item, ',') DS2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Ignore the previous entry, this is much closer:
SELECT
MAX(CASE WHEN DS2.ItemNumber = 1 THEN DS2.Item ELSE '' END) AS Id,
MAX(CASE WHEN DS2.ItemNumber = 2 THEN DS2.Item ELSE '' END) AS IdProduct,
MAX(CASE WHEN DS2.ItemNumber = 3 THEN DS2.Item ELSE '' END) AS FirstName,
MAX(CASE WHEN DS2.ItemNumber = 4 THEN DS2.Item ELSE '' END) AS LastName
FROM dbo.DelimitedSplit8K(@text, '|') DS1
CROSS APPLY dbo.DelimitedSplit8K(DS1.Item, ',') DS2
GROUP BY DS1.ItemNumber
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2020 at 3:58 pm
too late!
January 30, 2020 at 4:30 pm
thanks a lot scott. it helped me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply