[Resolved] simple "order by" question

vinny199

I need to understand the following.

I have a recordset that orders by $address

My problem is that the addresses inputed in the database start with the house number, such as: 1, kings road

I need the ranking to start instead with the first letter, and omit the number, otherwise i get silly ranking like:

1 kings road
1 cress road
1 destination road
2 kings road
2 cress road
2 destination road

etc. this is a problem.

Is there a way to omit numbers in the order by $ address field?

Many thanks,

Vinny

paulnaj

Hi vinny,

You could a number of string (or regex) functions to remove leading chars, but, if this ordering is crucial, why not split the field in the database from $address to something like $road and $road_number, then just sort on $road.

Paul 🙂

devinemke

this might work depending on the exact formatting of all of your addresses:

SELECT *, SUBSTRING(address, LOCATE(' ', address)) AS substring FROM table ORDER BY substring

it basically sorts by the first character following the first space. again, may not work on some funky addresses.

tekky

simple enough..

ORDER BY RIGHT(address,(LENGTH(address)-LOCATE(' ', address)))

'1 kings way' = 11 chars...

' ' = char 2
11 - 2 = 9

'kings way' = 9 rightmost chars

[edit -- of course devin's method looks neater, points go to me for creativity though :p j/k --]

vinny199

Thank you all,

It works perfect.
I used Teky's code, seemed to give the best order, but all good.

paulnaj, I could not re-organize the address fields into separate fields as the database i'm working on has 17000+ properties listed!

unless you know a trick?

Many thanks again.

tekky

INSERT INTO current_table (street_number, street_name) SELECT LEFT(address, LOCATE(' ', address)), RIGHT(address,(LENGTH(address)-LOCATE(' ', address))) as street_name FROM current_table WHERE 1

I would think this would do exactly that for you 😉 would require some testing... but essentiially this would take your current address field and store it in 2 parts if you were to create them before hand...