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.
Author |
Topic |
joseln
Starting Member
11 Posts |
Posted - 2007-11-23 : 03:03:59
|
Hi,I have a field value in my table as 1ab2c3but i want to display in my select query as abc123pls suggest how to write the "order by" in my select query.thanks |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-23 : 03:15:29
|
Something like this:-- Prepare Sample datadeclare @t table( a varchar(10))insert @tselect '1' union allselect 'a' union allselect 'b' union allselect '2' union allselect 'c' union allselect '3'-- Final queryselect * from @torder by case when a like '[a-z]' then 1 else 2 end, a Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
joseln
Starting Member
11 Posts |
Posted - 2007-11-23 : 03:34:03
|
Hi, Thanks by the way, what 1 and 2 represent in the case statement of the order by clause.case when a like [a-z] then 1 else 2 endthanks again |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-23 : 03:49:46
|
1 and 2 represents..1 and 2 only!! You can consider them as rank since you want Alphabets higher in the order, I ranked them as rank 1 and anything other than that as rank 2.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-23 : 04:28:35
|
What if you have value like 'ab5'?MadhivananFailing to plan is Planning to fail |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-23 : 04:36:01
|
Well in that case OP has to tell what should be the ranking of 'ab5'? Whether along with alphabets, between alphabets and numbers or after numbers?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-23 : 06:24:26
|
select * from @torder by case when a not like '%[^0-9]%' then 2 else 1 end, len(a), a E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|