SQL query for substring

Question

SQL query for substring

sks_989

SSC Eights!

Points: 843
More actions
October 14, 2020 at 5:22 am

#3796987

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.

Viewing 6 posts - 1 through 5 (of 5 total)

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

DesNorton SSC-Insane Points: 24416 More actions · Answer 1

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;

How to post data/code on a forum to get the best help.

sks_989 SSC Eights! Points: 843 More actions · Answer 2

sks_989

SSC Eights!

Points: 843

October 14, 2020 at 5:13 pm

#3797245

is there any other way if anyone knows.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

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".

sks_989 SSC Eights! Points: 843 More actions · Answer 4

Thank you very much i need it like way without cross apply and it work perfectly as you maintain. I really appreciate it.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 5

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".