/*
	function: 
		numeric_order

	arguments: 
		@numeric_string - a string of mixed alpha and numeric values
		@max_digits	- the maximum length of digits to compare

	description:
		Function numeric_order creates an orderable string based on the "numeric" value of @numeric_string 
		which can be ordered alphabetically.
		
		Ideally the strings should really be broken up into constituent parts and ordered properly, 
		but occasionally you come across data where it's just not worth the while.
	
		eg 
			select title from regulations order by title
		returns:
	
		Regulation 1(a) section 3
		Regulation 11 section 100(b)
		Regulation 11 section 2(c)(iii)
		Regulation 2 section 1
		Regulation 21 section 3 (b)
	
		whereas
			select title from regulations order by dbo.numeric_order(title,10)
		returns:
	
		Regulation 1(a) section 3
		Regulation 2 section 1
		Regulation 11 section 2(c)(iii)
		Regulation 11 section 100(b)
		Regulation 21 section 3 (b)
	
		which is the order most users would expect.

		NOTE: because the original strings are mixed, the user may include alphas between digits which are to be 
		sorted alphabetically, which means the string must keep all alpha parts of the original string as is.

	expected output:
			select dbo.numeric_order('Part 1(b) subsection 1',4)
		returns
			Part 0001(b) subsection 0001					
		
	test data:
		use test
		--go

		if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		drop table [dbo].[regulations]
		--go
		
		create table regulations (id int identity(1,1), title varchar(200))
		--go
		
		insert into regulations (title) select 'Regulation 1(a) section 3'
		insert into regulations (title) select 'Regulation 11 section 100(b)'
		insert into regulations (title) select 'Regulation 11 section 2(c)(iii)'
		insert into regulations (title) select 'Regulation 2 section 1'
		insert into regulations (title) select 'Regulation 21 section 3 (b)'
		--go
		
		select title as [Incorrectly Ordered] from regulations order by title
		--go
		
		select title as [Correctly Ordered] from regulations order by dbo.numeric_order(title, 10)
		--go
		
		drop table [dbo].[regulations]
		--go

*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[numeric_order]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[numeric_order]
go

create function [dbo].[numeric_order](
      	@numeric_string as varchar(1000),
	@max_digits as int)  
	returns varchar(8000) as  
begin 
	declare @return varchar(8000)
	
	declare @part varchar(1000)
	declare @digit_position int
	declare @rest varchar(1000)
	declare @non_digit_position int
	declare @numeric_term varchar(1000)
	declare @after varchar(1000)
	declare @between varchar(1000)
	declare @first_time int
	declare @digits varchar(100)
	
	--create a string of zeros equal in length to max number of digits of enclosed numeric values
	set @digits = replace(space(@max_digits),' ','0')
	
	--handle length issues 
	--worst case scenario is a number every second character multiplied by padlen < 8000
	--which would potentially add len(@numeric_string)/2 * padlen characters - so subtract this from the string
	set @part = left(@numeric_string, ((len(@numeric_string)/2) * @max_digits))
	
	--starting values
	set @non_digit_position = 0
	set @first_time = 1
	set @return = ''
	
	--loop while not at end of string
	while ((@non_digit_position > 0 or @first_time > 0) and (len(@part) > 0))
	begin
		--if there are digits in the string
		set @digit_position = patindex('%[0-9]%', @part)
		if @digit_position > 0
		begin
			--get the part of the string after the first digit
			set @rest = substring(@part, patindex('%[0-9]%', @part) + 1, len(@part)-patindex('%[0-9]%', @part) + 1)
			set @non_digit_position = patindex('%[^0-9]%',@rest)
	
			--extract the string of digits 
			set @numeric_term = case 
						when @non_digit_position > 0 then substring(@part, @digit_position, @non_digit_position) 
						else substring(@part, @digit_position, len(@part) - @digit_position + 1) 
						end
	
			--keep track of the rest of the string after and between the digits
			set @after = substring(@part, @digit_position + len(@numeric_term), len(@part) - @digit_position + @non_digit_position)
			set @between = '' + substring(@part, 1, @digit_position - 1)
			
			--build return string
			set @return = @return + @between + right(@digits + @numeric_term, @max_digits)
		end
		else
		begin
			--no more digits, just add back the rest of the original string
			set @return = @return + @part
			set @after = ''
		end
	
		--iterate
		set @first_time = 0
		set @part = @after
	end
	
	return @return

end
go

declare @regulations table (id int identity(1,1), title varchar(200))

insert into @regulations (title) select 'Regulation 1(a) section 3'
insert into @regulations (title) select 'Regulation 11 section 100(b)'
insert into @regulations (title) select 'Regulation 11 section 2(c)(iii)'
insert into @regulations (title) select 'Regulation 2 section 1'
insert into @regulations (title) select 'Regulation 21 section 3 (b)'

select * from @regulations

select		title
from		(
			SELECT	title,
				dbo.fnfilterstring(title, '%[0-9]%', '#', 1) as data
			from	@regulations
		) as d
order by	cast(dbo.fnparsestring(-1, '#', data) as int),
		cast(dbo.fnparsestring(-2, '#', data) as int)

quote:

---

Originally posted by rrb
there once was a yak in australia
who tried very hard to regale ya*
with ordering code
from his southern abode
but it turned out that he was a failure

---