Regular Expression

[JohnEOasis]

I have a field that I need to sort on.
That field that could be a number or text.
It currently sorts numbers as 1 10 2 3 30 31 32 4 5 6 7... I need them to sort 1 2 3 4 5 6 7 8 9 10 11 etc...

Is there a way to do this with a regular expression? Such as adding x number of spaces to the number i.e.

{pseudocode}

if ISNUM{SQL field}

Select numbercount = {SQL field length}
Case numbercount  = {single digit} 
    add 3 spaces 
Case numbercount  = {double digit} 
    add 2 spaces 
Case numbercount  = {tripple digit} 
    add 1 space 
Default
    add none

 

[gmmastros]

I would suggest this (instead of regular expressions)

Declare @Temp Table(Data VarChar(10))

Insert Into @Temp Values('1') 
Insert Into @Temp Values('10') 
Insert Into @Temp Values('2') 
Insert Into @Temp Values('3') 
Insert Into @Temp Values('30') 
Insert Into @Temp Values('31') 
Insert Into @Temp Values('32') 
Insert Into @Temp Values('4') 
Insert Into @Temp Values('5') 
Insert Into @Temp Values('6') 
Insert Into @Temp Values('7')

[green]-- Alpha sort[/green]
Select * From @Temp Order By Data

[green]-- Number sort[/green]
Select * From @Temp ORder By Right(Replicate('0', 10) + Data, 10)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[softhemc]

If the field contains a combination of numbers and text then as a slight extension to the Order By clauses provided by gmmastros

Select * From @Temp Order By 
	Case When IsNumeric(data) = 1 Then Right(Replicate('0', 10) + Data, 10) Else Data End