Re: Sort text field leading zeroes
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Mon, 18 Mar 2019 11:10:47 +0000
Message-ID: <q6nubn$hoj$11_at_dont-email.me>
>> 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 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...
>>
>>
>>
Date: Mon, 18 Mar 2019 11:10:47 +0000
Message-ID: <q6nubn$hoj$11_at_dont-email.me>
On 17/03/2019 10:25, Luuk wrote:
> On 16-3-2019 23:34, The Natural Philosopher wrote:
>> 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 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...
>>
>>
>>
> > And i thought i was the only one making lots of typing errors..... >
nope, normally I edit and correct tho.
> ;)
>
-- The New Left are the people they warned you about.Received on Mon Mar 18 2019 - 12:10:47 CET