Split Column On Last Hyphen From String

Question

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')

Answer 1

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;

Answer 2

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       |
+----+---------------------------------+-----------------------------+-----------+

Answer 3

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:

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