CODEWITHSUNDEEP
databasemysql
iagdotme
iagdotme

Reputation: 993

Ordering by the first alphabetical char in a column in MYSQL

A table in a MYSQL database has address details- eg... add1, add2, add3, district, postalTown, country

Ordering by postal town is usually fine, but some details have numbers in the postalTown column. For example 1420 Territet or 3100 Overijse. This will mean these will appear at the top above Aberdeen or Bristol. Is there a way of ordering by postalTown but by the first alphabetical character? That would mean the order of the above would be- Aberdeen, Bristol, Overijse, Territet

Thanks

Upvotes: 4

Views: 653

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146409

Write an expression that will return the first alphabetical character, then just Order By [that 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 want the rows sorted by the entire city name, and not just by the first character (as question title specifies) then 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',''))

Above is a guess (I haven't tried it), but the idea is first delete all numeric characters from the column value, then take the first character of whatever is remaining.

Also, if this works, and if you have any development access to the dataabse, (thinking DRY principle), I would add a computed column to this table, (or a separate view against the table), that is defined to use the above expression, so that this "extraction" of the town name is available to all other code that might want to access it without copying this expression everywhere you may need it..

Upvotes: 2

Chris Kannon
Chris Kannon

Reputation: 6091

You could write a stored function which returns the remainder of the column starting at the first alphabetic character (perhaps using REGEXP to find that index). Then order by the stored function.

Edit: instead of regexp in your function, depending on data format you could do a 'substring_index' on ' ' (space) and return the index of the first space, then call substring to return the remainder of the string after the first space.

Once you've created a stored function to return the string following the numbers, you can utilize it like this:

order by yourfunctionname(postalTown)

Stored Functions

Upvotes: 2

Unsliced
Unsliced

Reputation: 10552

Create a view on the table, making whatever translations you need, and then query against the view?

Upvotes: 1

George Johnston
George Johnston

Reputation: 32258

First thing that comes to mind to me would to do the following on my ORDER BY, obviously, adding numbers 0 through 9. You'll notice crappy schemas produce crappy solutions. :) As the gentleman said above, you should probably think about a redesign of how you are storing your town data.

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

Upvotes: 1

Related Questions