Sort Column From Extracted Word (T-SQL)
Posted: November 2, 2012 Filed under: SQL Server 2005, SQL Server 2008 Leave a commentThere 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