Get Min & Max values of a string

[celestine]

I have a field 'zip' in a table in my database that records values in the form on 'something/023/something', 'anything/346/somemore','kztt/1984/asdg'

I want to get the min and max values of this field in particular the numbers in the string. Initially, I simply run a query to get the min and max value of this field which works until the numbers in the string went to 4-digits instead of the usual 3.

Example query: select min(zip) as min, max(zip) as max from $dbtable

Then I run an explode function to retrieve the numbers in the string.

This query now return values like min=999, max=1000 instead, when the min number should be 023 and max is 1984

Can someone point me to the right direction? Thanks so much.

[Tangerine Dream]

Then I run an explode function to retrieve the numbers in the string.
Hi, if you already use PHP' explode(), then you should use further use PHP to get MAX and MIN values using max() & min() functions on array values, not SQL

[celestine]

Thank you so much, sorry for the late reply. I had alot of trouble accessing this forum as one of my IPs is on the ban list. I'll give it a try. Thank you for answering.

[Kid Charming]

You should use SQL for this sort of thing, especially if you're only wanting the min and max existing values. Chances are, you should be splitting your data up into three separate fields instead of keeping them in one field. Barring that, something like this should work:


SELECT
MAX(
SUBSTRING(
yourfield,
LOCATE('/',yourfield)+1,
LOCATE('/',yourfield,LOCATE('/',yourfield)+1) - (LOCATE('/',yourfield)+1)
)+0) AS mymax
,MIN(
SUBSTRING(
yourfield,
LOCATE('/',yourfield)+1,
LOCATE('/',yourfield,LOCATE('/',yourfield)+1) - (LOCATE('/',yourfield)+1)
)+0) AS mymin
FROM
yourtable;


Note that it will drop your leading zeros, since we have to convert the value from a string to and integer. There are also probably nicer ways to do it, but this is the first one that came to me.