Split Column On Last Hyphen From String

Johnathan Simpson 586 Reputation points
2021-01-28T17:13:05.933+00:00

I have a varchar(100) field that can have 1 or it could have 5 hyphens in it.

I need a way to find the last hyphen in the field, and split the data. Meaning, if we have red-green-blue123.23-purple

I would want to run the query
Select
firstpart
,secondpart

which would give me the results
red-green-blue123.23
purple

The issue I face is that the field could contain 0 hyphens or a max of 5. How can I write syntax to parse out the data like above with an unknown number of hyphens in the column?

my version is
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation

Sample DDL is

Create Table #Test

(  
sanitizeme varchar(100)
)

Insert Into #Test Values
('abc-123-456-789'), ('abc-123'), ('arm-leg.123-4.8234  .18-race-bat')
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-01-28T19:22:03.17+00:00

    Try this:

    DECLARE @Test TABLE (
        sanitizeme varchar(100)
    );
    INSERT INTO @Test VALUES
    ('abc'),('abc-123-456-789'), ('abc-123'), ('arm-leg.123-4.8234 .18-race-bat');
    
    SELECT 
        sanitizeme, 
        REVERSE(SUBSTRING(REVERSE(sanitizeme), CHARINDEX('-', REVERSE(sanitizeme)) + 1, LEN(REVERSE(sanitizeme)))) AS FirstPart,
        CASE WHEN CHARINDEX('-', sanitizeme) = 0 THEN '' ELSE REVERSE(SUBSTRING(REVERSE(sanitizeme), 1, CHARINDEX('-', REVERSE(sanitizeme)) - 1)) END AS SecondPart
    FROM @Test;
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2021-01-28T18:00:08.297+00:00

    Please check the following solution.
    It supports unlimited number of hyphens.
    It is using XML an XQuery.
    XML and XQuery data model is based on ordered sequences. That's why it is so easy to get first, last, or any other item in a sequence.

    SQL

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(1000));
    INSERT INTO @tbl (tokens) VALUES
    ('red-green-blue123.23-purple'),
    ('abc-123-456-789'), 
    ('abc-123'), 
    ('OneAndOnly'), 
    ('arm-leg.123-4.8234 .18-race-bat');
    -- DDL and sample data population, end
    
    DECLARE @separator CHAR(1) = '-';
    
    ;WITH rs AS
    (
       SELECT * 
          , TRY_CAST('<root><r>' + 
             REPLACE(tokens, @separator, '</r><r>') + 
             '</r></root>' AS XML) AS xmldata
       FROM @tbl
    )
    SELECT id, tokens 
     , REPLACE(xmldata.query('if (count(/root/r) gt 1) then data(/root/r[position() ne last()]) 
        else data(/root/r[1])').value('(.)', 'VARCHAR(100)'),SPACE(1),@separator) AS AllButLastToken
     , xmldata.query('if (count(/root/r) gt 1) then data(/root/r[last()])
        else ()').value('(.)', 'VARCHAR(100)') AS LastToken
    FROM rs;
    

    Output

    +----+---------------------------------+-----------------------------+-----------+
    | id |             tokens              |       AllButLastToken       | LastToken |
    +----+---------------------------------+-----------------------------+-----------+
    |  1 | red-green-blue123.23-purple     | red-green-blue123.23        | purple    |
    |  2 | abc-123-456-789                 | abc-123-456                 | 789       |
    |  3 | abc-123                         | abc                         | 123       |
    |  4 | OneAndOnly                      | OneAndOnly                  |           |
    |  5 | arm-leg.123-4.8234 .18-race-bat | arm-leg.123-4.8234-.18-race | bat       |
    +----+---------------------------------+-----------------------------+-----------+
    

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-01-29T02:58:17.713+00:00

    Hi @Johnathan Simpson

    The last hyphen in the string is equivalent to the first hyphen after the string is reversed.So the problem becomes simple, please refer to:

    select sanitizeme  
    ,reverse(right(reverse(sanitizeme),len(sanitizeme)-charindex('-',reverse(sanitizeme)))) as firstpart  
    ,reverse(left(reverse(sanitizeme),charindex('-',reverse(sanitizeme))-1)) as secondpart  
    from #test  
    --Or  
    ;with cte  
    as(select sanitizeme,reverse(sanitizeme) rs from #test)  
      
    select sanitizeme  
    ,reverse(right(rs,len(sanitizeme)-charindex('-',rs))) as firstpart  
    ,reverse(left(rs,charindex('-',rs)-1)) as secondpart  
    from cte  
    

    Output:
    61617-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our [documentation][2] to enable e-mail notifications if you want to receive the related email notification for this thread. [2]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html

    0 comments No comments