Geek Answers Handbook

Order by first char in column in MYSQL

The MYSQL database table contains the address data - for example ... add1, add2, add3, district, postalTown, country

An order for a postal city is usually fine, but some details are numbered in the postalTown column. For example, 1420 Territet or 3100 Overijse. This will mean that they will appear at the top of Aberdeen or Bristol. Is there a way to sort by postal order but first alphabetical character? This would mean that the order above would be: Aberdeen, Bristol, Overseas, Territory

thanks

+4
source share
4 answers

Write an expression that returns the first alphabetic character, and then just Order By [this expression]

Order By substring(LTrim( Replace(Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(Replace( colname, '1', ''),'2',''),'3',''),'4,''),'5', ''), '6',''),'7',''),'8',''),'9',''),'0','')) 1,1)

If you need strings sorted by the whole city name, and not just by the first character (as indicated in the question header), use this:

  Order By LTrim( Replace(Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(Replace( colname, '1', ''),'2',''),'3',''),'4,''),'5', ''), '6',''),'7',''),'8',''),'9',''),'0',''))

This assumption is higher (I have not tried it), but the idea first removes all the numeric characters from the column value, and then takes the first character of what remains.

Also, if this works, and if you have any development access for dataabse (DRY principle), I would add a computed column to this table (or a separate view against the table), which is defined as using the above expression, so that this “ extraction ” of the city name is available for all other code that you might want to access, without copying this expression in everything that you might need.

+2
source

You can write a stored function that returns the rest of the column starting with the first letter character (possibly using REGEXP to find this index). Then sort by stored function.

Edit: instead of the regular expression in your function, depending on the data format, you can make "substring_index" in the "" (space) field and return the index of the first space, and then call the substring to return the rest of the line after the first space.

Once you have created a saved function to return the line following the numbers, you can use it as follows:

 order by yourfunctionname(postalTown)

Saved Functions

+2
source

The first thing that comes to my mind will do the following on my ORDER BY, obviously adding numbers from 0 to 9. You will notice that crappy circuits create crappy solutions. :) As the gentleman said above, you should probably consider redesigning how you store your city data.

 ORDER BY REPLACE(REPLACE(REPLACE(FieldName, '1', ''),'2',''),'3','') ETC.
+1
source

Create a view in the table by making the necessary translations, and then request a view?

+1
source

More articles:


All Articles