CODEWITHSUNDEEP
sqlt-sql
PicoDeGallo
PicoDeGallo

Reputation: 608

Return everything before and after quotations in SQL

I have a single column that contains information that I need to have split up into three separate columns. I know that I need to use the SUBSTRING function, but I'm honestly at a relative loss as to how I would go about it. Could anyone offer some insight? Here is what the current column being returned looks like:

| Change                                                 |
----------------------------------------------------------
| changed status from "new" to "in progress"             |
| changed ORT status from "in progress" to "Code Review" |
| changed MileStone from "1.1" to "1.2"                  |

Here are how I need the results to return:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          | in progress |
| changed ORT status from | in progress  | Code Review |
| changed MileStone from  | 1.1          | 1.2         |

Thank you in advance!

Upvotes: 0

Views: 55

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Also working solution:

SELECT  SUBSTRING(n, 1, CHARINDEX('"', n) - 1) AS ChangeType ,
        SUBSTRING(n, CHARINDEX('"', n) + 1,
                  CHARINDEX('"', n, CHARINDEX('"', n) + 1) - CHARINDEX('"', n) - 1) AS ChangeFrom ,
        REPLACE(SUBSTRING(n, PATINDEX('% to %', n) + 5, LEN(n)), '"', '') AS ChangeTo
FROM    ( VALUES ( 'changed status from "new" to "in progress"' ),
        ( 'changed ORT status from "in progress" to "Code Review" '),
        ( 'changed MileStone from "1.1" to "1.2"') ) t ( n )

Upvotes: 0

Stephan
Stephan

Reputation: 6018

You could try using CHARINDEX and SUBSTRING

    WITH CTE
    AS
    (
        SELECT change
        FROM 
        (
            VALUES  ('changed status from "new" to "in progress"'),
                    ('changed ORT status from "in progress" to "Code Review"'),
                    ('changed MileStone from "1.1" to "1.2"')
        ) A(change)
    )

    SELECT  change,
            SUBSTRING(change,q1,q2 -q1 -1),
            SUBSTRING(change,q3,q4 -q3 -1)
    FROM CTE
    CROSS APPLY (SELECT CHARINDEX('"',change,00) + 1) CA1(q1)
    CROSS APPLY (SELECT CHARINDEX('"',change,q1) + 1) CA2(q2)
    CROSS APPLY (SELECT CHARINDEX('"',change,q2) + 1) CA3(q3)
    CROSS APPLY (SELECT CHARINDEX('"',change,q3) + 1) CA4(q4)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This should work, assuming that the words from and to don't appear elsewhere in the string:

select left(change, chardinex(' from ', change) + 5) as changeType,
       replace(substring(change, charindex(' from ', change) + 5,
                         charindex(' to ', change) - charindex(' from ', change) - 5
                        ), '"', '') as ChangeFrom,
       replace(right(change, charindex(' ot ', reverse(change)), '"', '') as changeTo

Upvotes: 1

Related Questions