Re: Sort text field leading zeroes

[Quoted] On 3/16/19 12:27 AM, vjp2.at_at_at.BioStrategist.dot.dot.com wrote:
> Ok, LPAD if it was a number, but I have a field "3 St" "40 St" and "500 St"
> and I want to ORDER them. I'm thinking extract the number, pad it then
> reappend? Is there an easier way?

[Quoted] Keep in mind that this will be slow and get slower with the amount of data, a redesign of your database and application could make it better if having the street number in it's own column.

You need to use regex* to get out the street name part, don't forget to use ^ and $ to mark start and end of the string as this can help speed wise if the regex don't have to look at the whole string.

Don't forget that the regex pattern should be able to give you a result for other types of addresses which lacks a street/road number and if you will add international addresses, the street number can be in the end of the street/road name.

• https://dev.mysql.com/doc/refman/5.7/en/regexp.html

-- 

  //Aho