Sorting from mysql.... 2

[rab54]

Hello again gurus -

I am pulling data out and sorting on the postcode - but have been asked to put it in 'proper' order - ie non-acii-betically ..

I need -
BB1
BB2
BB10
BB25

NOT -
BB1
BB10
BB2
BB25

Can we do this in the 'select' statement ?

cheers once again guys

Rab

 

[TonyGroves]

Are these UK postcodes, like 'AB12 3CD', 'A1 2BC', 'A1B 2CD', 'A12 3BC'?

If so, how about something like:
[tt]
SELECT *
FROM tbl
ORDER BY
IF(
code RLIKE '^[A-Z]{2}[0-9]{2} ',
code, [/tt]# e.g. 'AB12 3CD' -> 'AB12 3CD'[tt]
IF(
code RLIKE '^[A-Z][0-9]{2} ',
INSERT(code,2,0,' '), [/tt]# e.g. 'A12 3BC' -> 'A 12 3BC'[tt]
INSERT(code,2,0,' 0') [/tt]# e.g. 'A1 2BC' -> 'A 01 2BC',[tt]
[/tt] 'A1B 2CD' -> 'A 01B 2CD'[tt]
)
)
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Sorry, I'll rephrase that!

Are these UK postcodes, like 'AB12 3CD', 'A1 2BC', 'A1B 2CD', 'A12 3BC', 'AB1 2CD'?

If so, how about something like:
[tt]
SELECT *
FROM tbl
ORDER BY
IF(
code RLIKE '^[A-Z]{2}[0-9]{2} ',
code, [/tt]# e.g. 'AB12 3CD' -> 'AB12 3CD'[tt]
IF(
code RLIKE '^[A-Z]{2}[0-9] ',
INSERT(code,3,0,'0'), [/tt]# e.g. 'AB1 2CD' -> 'AB01 2CD'[tt]
IF(
code RLIKE '^[A-Z][0-9]{2} ',
INSERT(code,2,0,' '), [/tt]# e.g. 'A12 3BC' -> 'A 12 3BC'[tt]
INSERT(code,2,0,' 0') [/tt]# e.g. 'A1 2BC' -> 'A 01 2BC',[tt]
[/tt] 'A1B 2CD' -> 'A 01B 2CD'[tt]
)
)
)
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[rab54]

Cheers for that Tony -

I will have a look at it today and let you know ......


Rab

 

[rab54]

Tony - I bow to you oh wise one ;-)

It works like a dream !

I will read up on RLIKE and REGEXP - not used these within (my)sql before !

cheers mate

Rab

 

[JohnnnyB]

I was trying to do "ORDER BY ip" query and failed.

SELECT ip FROM test ORDER BY IF (ip RLIKE '\\.[0-9]{1}$', INSERT(ip,8,2,'0'), INSERT(ip,8,1,'0'))


I get ip numbers with one digit at the end first, but they are not in ascending order. Why?

10.0.0.6
10.0.0.8
10.0.0.9
10.0.0.1
10.0.0.2
10.0.0.3
10.0.0.4
10.0.0.5
10.0.0.10
10.0.0.11
10.0.0.12
10.0.0.13
10.0.0.14
10.0.0.15
10.0.0.16
10.0.0.20
10.0.0.21
10.0.0.25
10.0.0.18

 

[TonyGroves]

Try instead:
[tt]
SELECT ip
FROM t
ORDER BY
IF(
ip RLIKE '\\..$',[/tt] # or '\\.[0-9]{1}$'[tt]
INSERT(ip,8,0,'0'),
ip
)
[/tt]
Or, for simplicity (and probably speed):
[tt]
SELECT ip
FROM t
ORDER BY IF(length(ip)=8,INSERT(ip,8,0,'0'),ip)
[/tt]

These solutions both assume that all your IP addresses are in the form 10.0.0.x or 10.0.0.xx.

-----
ALTER world DROP injustice, ADD peace;