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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s