Index a VARCHAR like an INT 1

[djj55]

Hello, SQL2008R2
I have a column that is VARCHAR(10) with a reference number. Unfortunately there are some reference numbers with a character. I want to be able to index the reference number so that 100021999, 100022000 does not have 100022 between it. The reference numbers with the character can be at the start or the finish but I would like to sort the rest like an integer.

Current order:[tt]
100000
100000001
100021999
100022
100022000
100022642
100023
100024
99999
D67332[/tt]

The order I would like is:[tt]
99999
100000
100022
100023
100024
100000001
100021999
100022000
100022642
D67332[/tt]

If I did not have the character values I would cast as integer.

Thanks for suggestions,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

If you pad your numbers with 0's on the left, they will sort properly for you. Please understand that the code I show below may have performance implications because there is no index that SQL Server can use to speed this up.

Declare @Temp Table(Data VarChar(20))

Insert into @temp Values('100000')
Insert into @temp Values('100000001')
Insert into @temp Values('100021999')
Insert into @temp Values('100022')
Insert into @temp Values('100022000')
Insert into @temp Values('100022642')
Insert into @temp Values('100023')
Insert into @temp Values('100024')
Insert into @temp Values('99999')
Insert into @temp Values('D67332')

Select *
From   @Temp
Order By IsNumeric(Data) DESC, Right('00000000000000000000' + Data, 20)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

It's possible to create a computed column in your table, and then index that computed column. This should speed things up a bit for you. You should be aware that adding a computed column with an index also has minor performance implications, but it's likely to be better than my previous suggestion.

Alter Table [!]YourTableName[/!]
Add SortOrder As 
       Convert(Char(1), -IsNumeric(Data)) 
       + Right('00000000000000000000' + Data, 20)

Create Index idx_[!]YourTableName[/!]_SortOrder On [!]YourTableName[/!](SortOrder)

Select Data
From   [!]YourTableName[/!]
Order By SortOrder

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George.
Did not think of the fill-in. The column might be useful for a new table but the old table (legacy) has 250 plus columns already, which we are working on reducing.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!