Re: Sort text field leading zeroes

From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Sat, 16 Mar 2019 22:34:57 +0000
Message-ID: <q6jtmi$gua$1_at_dont-email.me>

[Quoted] On 16/03/2019 09:13, J.O. Aho wrote:
> 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
>
>
Or you could simpy do as I usaully do, seleft the who,e damed table into virtual memory sarray of striuctures, using a C program, and write a [Quoted] function to sort it antway yu want.

I generally fiud that it is quicker to write debug compile and run such than work out the acrcane SQL or rexep required, and then wait the whole 24 hours while it runs...

-- 
"Nature does not give up the winter because people dislike the cold."

― Confucius

Received on Sat Mar 16 2019 - 23:34:57 CET