sql - How can I sort A1,A2,A3,B1,B2,B3,...,AA1,AA2,AA3 in MySQL? -

-

i have data set looks this:

+--------+ | square | +--------+ | a1     | | a10    | | a2     | | a3     | | a4     | | a5     | | a6     | | a7     | | a8     | | a9     | | b1     | | b10    | | b2     | | b3     | | b4     | | b5     | | b6     | | b7     | | b8     | | b9     |

...

| aa1    | | aa10   | | aa2    | | aa3    | | aa4    | | aa5    | | aa6    | | aa7    | | aa8    | | aa9    | +--------+

the prefix runs a#-z#, goes aa#-zz# , continues, maximum of 2 letters (i.e. never go on zz). number suffix can length (i.e. a1,a10,a100,a1000,etc.).

how can sort these , have result set come out follows:

+--------+ | square | +--------+ | a1     | | a2     | | a3     | | a4     | | a5     | | a6     | | a7     | | a8     | | a9     | | a10    | | b1     | | b2     | | b3     | | b4     | | b5     | | b6     | | b7     | | b8     | | b9     | | b10    |

...

| aa1    | | aa2    | | aa3    | | aa4    | | aa5    | | aa6    | | aa7    | | aa8    | | aa9    | | aa10   | +--------+

you need restructure table or else have inefficient query this,

select  square    table1 order                    case when square regexp '^[a-z]{2}'             1              else 0         end asc,         case when square regexp '^[a-z]{2}'             left(square, 2)             else left(square, 1)         end asc,         case when square regexp '^[a-z]{2}'             cast(right(square, length(square) - 2) signed)             else cast(right(square, length(square) - 1) signed)         end asc

or using if

select  square    table1 order   square regexp '^[a-z]{2}' asc,            if(square regexp '^[a-z]{2}', left(square, 2), left(square, 1)),            cast(if(square regexp '^[a-z]{2}', right(square, length(square) - 2), right(square, length(square) - 1)) signed)

Comments

Post a Comment

Popular posts from this blog

Delphi XE2 Indy10 udp client-server interchange using SendBuffer-ReceiveBuffer -

-
i use delphi xe2 , indy10 udp protocol. can't receive server echo on client side if use receivebuffer method. got "socket error # 10040" although send small echo message server client. console application illustrate problem below. in advance. program project1; {$apptype console} {$r *.res} uses system.sysutils, idglobal, idbasecomponent, idcomponent, idsockethandle, idudpclient, idudpserver, idudpbase, idstack; type tudp_serv = class(tidudpserver) procedure udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); end; var udpserver: tudp_serv; udpcl: tidudpclient; bsnd, brcv: tbytes; s: string; k: integer; //============================================================================== procedure tudp_serv.udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); begin writeln(' server read: ' + tohex(adata, length(adata))); abinding sendto(peerip, peerport, adat...
Read more

Qt ActiveX WMI QAxBase::dynamicCallHelper: ItemIndex(int): No such property in -

-
i new windows programming. here goes code objiwbemlocator = new qaxobject("wbemscripting.swbemlocator"); objwmiservice = objiwbemlocator->querysubobject("connectserver(qstring&,qstring&)",qstring("."),qstring("root\\cimv2")); qaxobject* returnlist = objwmiservice->querysubobject("execquery(qstring&)", qstring("select * %1").arg(domain)); qaxobject* result = returnlist->querysubobject("itemindex(int)", 0); i getting error on runtime qt activex wmi qaxbase::dynamiccallhelper: itemindex(int): no such property in but itemindex method exists msdn says the itemindex method not work collections not contain swbemobjects, such swbemmethodset, swbemnamedvalueset, swbemprivilegeset, swbempropertyset, , swbemqualifierset. what selecting ? have tried iterating on collection ? plus need minimum windows vista itemindex method. hope helps
Read more

Enable autocomplete or intellisense in Atom editor for PHP -

-
i'm first time user of atom editor php code. atom can autocomplete built-in functions of php. possible make editor autocomplete custom function written in separate file? function.php <?php function printsomething() { echo "hello world"; } index.php <?php require_once "function.php"; printsomething(); // autocompleted atom editor while typing thanks in advance. there text file in %userprofile%.atom called snippets.cson. in there can add snippet. more this: http://flight-manual.atom.io/using-atom/sections/snippets/ but need think can use later on other files well. otherwise need copy , paste.
Read more