Tricky MySQL query

Finding a number between x and y

[DrDoc]

A certain field can have the following values:

¦ (=NULL)

x¦

¦y

x¦y
... where x and y can be any number (x being the lower).

Now, I want to check whether any given number (n) matches the field.

It should always be a match if:

the field is ¦

n is higher or equal to x (and y is blank)

n is lower or equal to y (and x is blank)

n is higher or equal to x and lower or equal to y (when both values are set)

Now, how can I do this in a SELECT query? I figure that I need REGEXP and stuff, but I just can't seem to get it to work.

[BCMG_Scott]

Well first, why not have 2 field rather than putting them in one with a pipe seperator? Second, regexp is not an option in SQL. You could use the RIGHT, LEFT, LOCATE, and LENGTH functions to split and parse. I would still recommend 2 fields. If you have 2 field it would work like this.

select * from table
where 
(x is null and y is null) or
(x <= n and y is null) or
(x is null and y >= n) or
(x <= n and y >= n);

Scott Geiger

[DrDoc]

Well, I already have just one field... and it would be too much of a hassle to change that. And REGEXP is an option...

MySQL Regular Expressions [mysql.com]

Either way, I can't change it to two fields, so I need something that works the way it is now. Thanks anyway though.

[jatar_k]

Its too bad the pipe is in there, I assume it is a varchar field then?

I keep running into the problem that my brain keeps swapping into int compare mode. I also assume that no changes will be made to the format and there is a method to that particular madness. ;)

Not being a regex genius in any way shape or form I keep thinking of doing the work in the script but that may not make any sense depending on the number of rows returned.

[DrDoc]

Yes, it's a varchar field. And, well... I was thinking about doing it in the script (as a last resort), but I know I should be able to do it in the query. Depending on the query, it can return thousands of results!

[DrDoc]

I tried this, but it returned all results :(

SELECT * FROM table WHERE
(
field='¦'
OR
(
INSTR(field,'¦')!=1
AND
INSTR(field,'¦')!=LENGTH(field)
AND
(
LEFT(field, INSTR(field,'¦')-1)<=50
)
AND
(
SUBSTRING(field,INSTR(field,'¦')+1)>=50
)
)
OR
(
INSTR(field,'¦')=1
AND
(
SUBSTRING(field,INSTR(field,'¦')+1)>=50
)
)
OR
(
INSTR(field,'¦')!=LENGTH(field)
AND
(
LEFT(field, INSTR(field,'¦')-1)<=50
)
)
)

[DrDoc]

Ok, something was wrong in that query... (don't remember what) so, here's what I did to make it work:

SELECT * FROM table WHERE 
( 
field='¦' 
OR 
( 
INSTR(field,'¦')!=1 
AND 
INSTR(field,'¦')!=LENGTH(field) 
AND 
( 
LEFT(field, INSTR(field,'¦')-1)<='50' 
) 
AND 
( 
SUBSTRING(field,INSTR(field,'¦')+1)>='50' 
) 
)  
OR 
( 
INSTR(field,'¦')=1 
AND 
INSTR(field,'¦')!=LENGTH(field) 
AND 
( 
SUBSTRING(field,INSTR(field,'¦')+1)>='50' 
) 
)  
OR 
( 
INSTR(field,'¦')!=1 
AND 
INSTR(field,'¦')=LENGTH(field) 
AND 
( 
LEFT(field, INSTR(field,'¦')-1)<='50' 
) 
)  
)

[BCMG_Scott]

Well, I already have just one field... and it would be too much of a hassle to change that.

I understand, looks like it's gonna be a hassle either way.

And REGEXP is an option...

so it is! hmmm - guess I am so used to dealing with Oracle that I missed that in MySQL.

Scott

[daisho]

SELECT table.*,
IFNULL(SUBSTRING(field,0,POSITION('¦' IN field)-1),-999) AS lownumber,
IFNULL(SUBSTRING(field,POSITION('¦' IN field)+1),-999) AS highnumber
FROM table
WHERE [your number] BETWEEN lownumber AND highnumber;

This is just off the top of my head an you may have to do a little playing. If -999 is a valid value that you may be checking for then change it to a number out of range that you will be checking for.

This is a slow operation and like other people I'd highly recommened 2 columns. Running 6 functions (IFNULL,SUBSTRING,POSITION * 2) is a big penalty.

daisho.