SQL query for substring

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

     

     

     

     

     

  • 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;
  • is there any other way if anyone knows.

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

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

  • 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