October 14, 2020 at 5:22 am
I have data into table where i need to create 4 field from this one field.
Here is data
1024-----555-666-P
1025-----333-6666-I
1026-----33-453-P
i need data into 4 field like this way
1024 555 666 P
1025 333 6666 I
1026 33 453 I
I have fixed 5 (-----) after first data then i have 1 (-) for other data.
Is it possible to write into one SQL statement to get 4 field
Thanks in Advance.
October 14, 2020 at 6:53 am
You can use a splitter function, and a cross tab to get what you are looking for
DECLARE @TestData table (JoinedData varchar(50));
INSERT INTO @TestData ( JoinedData )
VALUES ( '1024-----555-666-P' )
, ( '1025-----333-6666-I' )
, ( '1026-----33-453-P' );
SELECT td.*
, Column1 = MAX(CASE WHEN spl.ItemNumber = 1 THEN spl.Item END)
, Column2 = MAX(CASE WHEN spl.ItemNumber = 2 THEN spl.Item END)
, Column3 = MAX(CASE WHEN spl.ItemNumber = 3 THEN spl.Item END)
, Column4 = MAX(CASE WHEN spl.ItemNumber = 4 THEN spl.Item END)
FROM @TestData AS td
/*** The code for the dbo.DelimitedSplit8K_LEAD function can be found here ... http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/ ***/
CROSS APPLY dbo.DelimitedSplit8K_LEAD(REPLACE(td.JoinedData, '-----', '-'), '-') AS spl
GROUP BY td.JoinedData;
October 14, 2020 at 5:13 pm
is there any other way if anyone knows.
October 14, 2020 at 6:05 pm
There's almost always an alternative in coding. If you don't want the CROSS APPLY, you can replace "dash_2" everywhere it appears in the SELECT with "CHARINDEX('-', string, PATINDEX('%-----%', string) + 5)". I used the CA to shorten / simplify the code.
;WITH sample_data AS (
SELECT * FROM (VALUES
--123456789!123456789@
('1024-----555-666-P'),
('1025-----333-6666-I'),
('1026-----33-453-P'),
('1026341-----3312-45356-PQR') --<<--<<-- added to verify the code is fully correct
) AS data(string)
)
SELECT
string,
LEFT(string, CHARINDEX('-', string) - 1) AS string1,
SUBSTRING(string, PATINDEX('%-----%', string) + 5, dash_2 - (PATINDEX('%-----%', string) + 5)) AS string2,
SUBSTRING(string, dash_2 + 1, LEN(string) - dash_2 - CHARINDEX('-', REVERSE(string))) AS string3,
RIGHT(string, CHARINDEX('-', REVERSE(string)) - 1) AS string4
FROM sample_data
CROSS APPLY (
SELECT CHARINDEX('-', string, PATINDEX('%-----%', string) + 5) AS dash_2
) AS ca1
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".
October 14, 2020 at 7:48 pm
Thank you very much i need it like way without cross apply and it work perfectly as you maintain. I really appreciate it.
October 14, 2020 at 7:52 pm
You're very welcome.
It would help everyone if you marked it as the "Answer". That way future readers know it's been resolved and they don't need to spend time on it (unless they deliberately want to try to find an improved method).
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".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply