Re: Sort text field leading zeroes

On 16-3-2019 23:34, The Natural Philosopher wrote:
> On 16/03/2019 09:13, J.O. Aho wrote:

[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?
>>
>> 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
>>
>>

And i thought i was the only one making lots of typing errors.....

;)

-- 
Luuk