Sort Column From Extracted Word (T-SQL)

There are multiple ways to extract a word from a string based on some delimiters. In this sample, we need to sort the table based on the word after the third hyphen.
Here is the solution I came up with XML:

create table #test (s_id int, InStr varchar(2000))
insert into #test values (1,'A-xxxx -Management -Security -Service -SUPPORT'),(2,'A-zzzz -Doctors -DENTIST'),(3,'B-ew -Consultants -PRACTICE-software-development')

SELECT s_id, InStr, S.a.value('(/h/r)[4]', 'NVARCHAR(50)') AS SortWord from (
SELECT s_id, InStr, Cast('<h><r>'+Replace(InStr,'-','</r><r>')+'</r></h>' as XML) colXML FROM #test) d
Cross Apply d.colXML.nodes('/h') S(a)
Order By SortWord

--Another solution with multiple CROSS APPLY by johnqflorida
--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3076741a-46a2-4cb3-be89-5faf358e5e76
Select *  from #test
Cross Apply (Select Charindex('-', InStr) as DashOne) CA1
Cross Apply (Select Charindex('-', Instr, DashOne + 1) as DashTwo) CA2
Cross Apply (Select Charindex('-', Instr, DashTwo + 1) as DashThree) CA3
--Cross Apply (Select Charindex('-', Instr, DashThree + 1) as DashFour) CA4
Cross Apply (Select SubString(Instr, DashThree + 1, 999) as SortWord) CA5
Order by SortWord

drop table #test

Advertisements

---