Split a string in SQL by hyphen in 2012 version

Question

I have multiple string in a column where I have get last string after column Below are three example like same I have different number hyphen that can occur in a string but desired result is I have string before last hyphen

1. abc-def-Opto
2. abc-def-ijk-5C-hello-Opto
3. abc-def-ijk-4C-hi-Build 
4. abc-def-ijk-4C-123-suppymanagement

Desired result set is

def
hello
hi
123

How to do this in SQL query to get this result set. I have MSSQL 2012 version Require a generic sql which can get the result set

If you can upgrade to SQL Server 2016 you could use `STRING_SPLIT` and pick the penultimate row from the result set. Or the answer [here](https://stackoverflow.com/questions/10914576/t-sql-split-string) has a similar function to `SPLIT_STRING` that works for SQL 2008 onwards — Peter Smith, Dec 13 '18 at 12:58

John Cappelletti · Answer 1 · 2018-12-13T13:28:37.083

1

There are many ways to split/parse a string. ParseName() would fail because you may have more than 4 positions.

One option (just for fun), is to use a little XML.

We reverse the string
Convert into XML
Grab the second node
Reverse the desired value for the final presentation

Example

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('abc-def-Opto')
,('abc-def-ijk-5C-hello-Opto')
,('abc-def-ijk-4C-hi-Build')
,('abc-def-ijk-4C-123-suppymanagement')

Select * 
      ,Value = reverse(convert(xml,'<x>'+replace(reverse(SomeCol),'-','</x><x>')+'</x>').value('x[2]','varchar(150)'))
 from @YourTable

Returns

SomeCol                             Value
abc-def-Opto                        def
abc-def-ijk-5C-hello-Opto           hello
abc-def-ijk-4C-hi-Build             hi
abc-def-ijk-4C-123-suppymanagement  123

edited Dec 13 '18 at 13:28

answered Dec 13 '18 at 13:14

John Cappelletti

79,615
7
44
66

Neat, nice solution – Peter Smith Dec 13 '18 at 13:26
@PeterSmith Thanks for the grin :) – John Cappelletti Dec 13 '18 at 13:26
Hi John, sleek solution but unfortunately the minute to touch XML in SQL Server the performance goes down the drain. Just out of curiosity I compared the execution plans for your query with mine and here are the results :) https://www.brentozar.com/pastetheplan/?id=rJs0Z1le4 . 98% of the cost in your query is coming from the XML function. I ran this test on about 50K rows. – M.Ali Dec 13 '18 at 13:45
@M.Ali The execution plan looks terrifying! However, 10 runs each using a sample of 1,000 newid()'s the avg performance was better. Mine was an average of 318ms while yours was 427ms – John Cappelletti Dec 13 '18 at 14:00
Yep, it is possible that the XML function will out perform anything else with smaller number of rows. But what I have noticed is as soon as the number of rows increase the performance of XML functions degrades and not in proportion of number of rows. For example if it takes 10 seconds to process 10K rows, it will take a lot longer than 50 seconds to process 50K rows. But anyway it was my observation, I thought it was worth sharing :) – M.Ali Dec 13 '18 at 14:07
@M.Ali Trust me when I say I'm not being defensive (I dig the challenge). I'm seeing 12.3 seconds for 50K rows via the XML and 10.8 seconds with yours. I'll happily cede that your approach is indeed better, but you have to admit, mine is prettier :) – John Cappelletti Dec 13 '18 at 14:21
1

Haha yours is definitely sleeker or "Prettier" as you put :) I said that in my very first comment – M.Ali Dec 13 '18 at 14:27

score 1 · Answer 2 · answered Dec 13 '18 at 13:33

Without getting into XML stuff, simply using string functions of sql server.

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('abc-def-Opto')
,('abc-def-ijk-5C-hello-Opto')
,('abc-def-ijk-4C-hi-Build')
,('abc-def-ijk-4C-123-suppymanagement');


SELECT * 
        ,RTRIM(LTRIM(REVERSE( 
            SUBSTRING(
                    SUBSTRING(REVERSE([SomeCol]) , CHARINDEX('-', REVERSE([SomeCol]))  +1 , LEN([SomeCol]) )
                     , 1 , CHARINDEX('-', SUBSTRING(REVERSE([SomeCol]) , CHARINDEX('-', REVERSE([SomeCol]))  +1 , LEN([SomeCol]) ) ) -1
                    )
                )))
FROM @YourTable

score 0 · Answer 3 · answered Dec 13 '18 at 13:17

i am not sure this script will exactly useful to your requirement but i am just trying to give an idea how to split the data

IF OBJECT_ID('tempdb..#Temp')IS NOT NULL
DROP TABLE #Temp
;WITH CTE(Id,data)
AS
(
SELECT 1,'abc-def-Opto'                 UNION ALL
SELECT 2,'abc-def-ijk-5C-hello-Opto'    UNION ALL
SELECT 3,'abc-def-ijk-4C-hi-Build'      UNION ALL
SELECT 4,'abc-def-ijk-4C-123-suppymanagement'
)
,Cte2
AS
(
SELECT Id, CASE WHEN Id=1   AND  Setdata=1 THEN data
                WHEN Id=2   AND  Setdata=2 THEN data
                WHEN Id=3   AND  Setdata=3 THEN data
                WHEN Id=4   AND  Setdata=4 THEN data
                ELSE NULL 
            END AS Data
FROM
(
SELECT  Id,
        Split.a.value('.','nvarchar(1000)') AS Data,
        ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS Setdata
FROM(
SELECT Id,
       CAST('<S>'+REPLACE(data ,'-','</S><S>')+'</S>' AS XML) AS data
FROM CTE
) AS A
CROSS APPLY data.nodes('S') AS Split(a)
)dt
)
SELECT * INTO #Temp FROM Cte2 

SELECT STUFF((SELECT DISTINCT ', '+ 'Set_'+CAST(Id  AS VARCHAR(10))+':'+Data
FROM #Temp WHERE ISNULL(Data,'')<>'' FOR XML PATH ('')),1,1,'')

Result

Set_1:abc, Set_2:def, Set_3:ijk, Set_4:4C

score 0 · Answer 4 · answered Dec 13 '18 at 14:30

You can do like

WITH CTE AS
(
  SELECT 1 ID,'abc-def-Opto' Str
  UNION
  SELECT 2, 'abc-def-ijk-5C-hello-Opto'
  UNION
  SELECT 3, 'abc-def-ijk-4C-hi-Build'
  UNION
  SELECT 4, 'abc-def-ijk-4C-123-suppymanagement'
)
SELECT ID,
       REVERSE(LEFT(REPLACE(P2, P1, ''), CHARINDEX('-', REPLACE(P2, P1, ''))-1)) Result
FROM (
       SELECT LEFT(REVERSE(Str), CHARINDEX('-', REVERSE(Str))) P1,
              REVERSE(Str) P2,
              ID
       FROM CTE
     ) T;

Returns:

+----+--------+
| ID | Result |
+----+--------+
|  1 | def    |
|  2 | hello  |
|  3 | hi     |
|  4 | 123    |
+----+--------+

Demo

Split a string in SQL by hyphen in 2012 version

4 Answers4

Result