sql server - MS Sql order by numeric parts of string -
i need order query numeric parts.
i have lot of rows looks like:
'abcd.1234.567' 'abcd.1234-2345' 'abcd.1234.1213.1'
so, want firs order first numeric part, second , therd. this:
'abcd.1234.567' 'abcd.1234.1213.1' 'abcd.1234-2345'
how can this?
upd: have tried use patindex function in order by, can figure out how numbers.
order s.product, (case when patindex('% ,.,-%', s.product)=0 0 else cast(substring(s.product, patindex('%[0-9]%', s.product), len(s.product)) int) end)
you use string split function
declare @sampledata table ( name varchar(100) ) insert @sampledata values ('abcd.1234.567'), ('abcd.1234-2345'), ('abcd.1234.1213.1')
your query be
select * @sampledata sd outer apply ( select cast(t.value int) part1 [dbo].[splitstring](replace(sd.name,'-', '.'),'.') t t.pos = 2 ) p1 outer apply ( select cast(t.value int) part2 [dbo].[splitstring](replace(sd.name,'-', '.'),'.') t t.pos = 3 ) p2 outer apply ( select cast(t.value int) part3 [dbo].[splitstring](replace(sd.name,'-', '.'),'.') t t.pos = 4 ) p3 order p1.[part1], p2.[part2], p3.part3
and string split function
create function [dbo].[splitstring] (@text varchar(max),@delimiter varchar(10)) returns table return ( select pos = row_number() on (order (select null)) ,value = ltrim(rtrim(b.i.value('(./text())[1]', 'varchar(max)'))) (select x = cast('<x>'+ replace(@text,@delimiter,'</x><x>')+'</x>' xml).query('.')) cross apply x.nodes('x') b(i) );
result:
name part1 part2 part3 --------------------------------------- abcd.1234.567 1234 567 null abcd.1234.1213.1 1234 1213 1 abcd.1234-2345 1234 2345 null
Comments
Post a Comment