Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sorting Capability on a column

Author  Topic 

InNomina
Starting Member

40 Posts

Posted - 2014-01-27 : 16:40:33
I need to sort on a column that has a mix of alpha/numeric...
o Actual Sort: A1, A100, A14, A2, A222, A25 …
o Favored Sort: A1, A14, A100, A2, A25, A222 …

Can this be done?

-------------------------
"If you never fail, you're not trying hard enough"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-27 : 17:28:32
Is that sample data representative of the actual data? Meaning, does the data always have a character followed by numbers or is it a mix?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 03:13:41
yep...it will sort it based on pattern present. are you looking at different sort order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

InNomina
Starting Member

40 Posts

Posted - 2014-01-28 : 11:43:09
Right now it does...A1, A100, A14, A2, A222, A25 …

What I need is A1, A14, A100, A2, A25, B1, B2, B3 etc.

I was thinking I might try derived columns but not sure what my options are...

-------------------------
"If you never fail, you're not trying hard enough"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 04:03:40
quote:
Originally posted by InNomina

Right now it does...A1, A100, A14, A2, A222, A25 …

What I need is A1, A14, A100, A2, A25, B1, B2, B3 etc.

I was thinking I might try derived columns but not sure what my options are...

-------------------------
"If you never fail, you're not trying hard enough"


for that try like this

ORDER BY LEFT(Col,PATINDEX('%[0-9]%',Col)-1),STUFF(Col,1,PATINDEX('%[0-9]%',Col)-1,'')*1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -