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? |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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" |
|
|
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 thisORDER BY LEFT(Col,PATINDEX('%[0-9]%',Col)-1),STUFF(Col,1,PATINDEX('%[0-9]%',Col)-1,'')*1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|