/*
create table t (a varchar(10))
insert t select '1a1'
insert t select '4*0'
insert t select '1aw0'
insert t select 's20'
insert t select 't02'
insert t select '11r1'
insert t select '11v4g7@4/'
1
02
4
10
11
20
111
select * from t order by len(a),a
a98a3455
select * from t order by a
select a,dbo.fn_StringNumSort(a) from t order by dbo.fn_StringNumSort(a)
select dbo.fn_StringNumSort('102a')
*/
Alter Function dbo.fn_StringNumSort
(
@mStringVar Varchar(max)
)
Returns Varchar(max)
As
Begin
--1. Shred chars and numbers
--2. Concat 10000000000 to numbers so that the total length stays 11 - Substring('10000000000', 1, 11-Len(Set of numbers))
--3. Cases - abc13ab, a1bc13a1b1, 1, a, 02, 1, 10
-- declare @mStringVar varchar(max)
-- select @mStringVar = '02'
declare @result varchar(max), @VarLen int, @StartPointer int, @EndPointer int;
select @result = '', @VarLen = Len(@mStringVar), @StartPointer = 1, @EndPointer = 1;
While @EndPointer <= @VarLen
Begin
--48 to 57 is the ASCII for numbers 0-9; find the first position of number
If ASCII(Substring(@mStringVar,@EndPointer,1)) >= 48 And ASCII(Substring(@mStringVar,@EndPointer,1)) <= 57 AND @EndPointer <= @VarLen
Begin
-- select 'num', @StartPointer, @EndPointer, ASCII(Substring(@mStringVar,@EndPointer,1))
--While there are numbers, move the pointer ahead till it reaches the next char
While ASCII(Substring(@mStringVar,@EndPointer,1)) >= 48 AND ASCII(Substring(@mStringVar,@EndPointer,1)) <= 57 AND @EndPointer <= @VarLen
select @EndPointer = @EndPointer + 1
--Handle if there is single number or multiple number - single number will return 0 for (@EndPointer-@StartPointer)
If @StartPointer < @EndPointer
select @result =
CASE LEN(@result)
When 0 Then Substring('10000000000', 1, 11-Len(Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer))) + Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer)
-- Else @result + '-' + Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer)
Else @result + Substring('10000000000', 1, 11-Len(Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer))) + Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer)
END
Else If @StartPointer = @EndPointer
select @result =
CASE LEN(@result)
When 0 Then Substring('10000000000', 1, 11-Len(Substring(@mStringVar,@StartPointer,1))) + Substring(@mStringVar,@StartPointer,1)
-- Else @result + '-' + Substring(@mStringVar,@StartPointer,1)
Else @result + Substring('10000000000', 1, 11-Len(Substring(@mStringVar,@StartPointer,1))) + Substring(@mStringVar,@StartPointer,1)
END
--Mark the start of the chars
select @StartPointer = @EndPointer
-- select @StartPointer, @EndPointer
-- select @result
End
Else
Begin
-- select 'char', @StartPointer, @EndPointer, ASCII(Substring(@mStringVar,@EndPointer,1)),Substring(@mStringVar,@EndPointer,1)
--While there are chars, move the pointer ahead till it reaches the next number
While (ASCII(Substring(@mStringVar,@EndPointer,1)) < 48 Or ASCII(Substring(@mStringVar,@EndPointer,1)) > 57) AND @EndPointer <= @VarLen
select @EndPointer = @EndPointer + 1
--Handle if there is single number or multiple chars - single chars will return 0 for (@EndPointer-@StartPointer)
If @StartPointer < @EndPointer
select @result =
CASE LEN(@result)
When 0 Then Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer)
Else @result + Substring(@mStringVar,@StartPointer,@EndPointer-@StartPointer)
END
Else If @StartPointer = @EndPointer
select @result =
CASE LEN(@result)
When 0 Then Substring(@mStringVar,@StartPointer,1)
Else @result + Substring(@mStringVar,@StartPointer,1)
END
--Mark the start of the numbers
select @StartPointer = @EndPointer
-- select @StartPointer, @EndPointer
-- select @result
End
End
Return (@result)
End
