REGEXP - selecting mid-value range 1

[jorgen180]

Hi everyone.


I need a kind of a specific query, that would select a range of records.

Having a MySql table containing records with this pattern:


BRF-100-TLE
BRF-101-TLE
BRF-102-TLE
...
BRF-999-TLE

+ other sequences of this kind, for e.g.:

ARF-100-TLH
ARF-101-TLH
ARF-102-TLH
...
ARF-999-TLH



I'd like to select just the records inside the mid-value range, in other words...

...If user wants to select records with BRF as a prefix and TLE as a suffix, having mid-value range from 100-200,

SELECT result would be:

BRF-100-TLE
BRF-101-TLE
...
BRF-200-TLE


(although there are values beyond BRF-200-TLE)



Been experimenting with REGEXP:

SELECT from my_table
WHERE UserID REGEXP "^BRF-[100-200]"


...but it's beyond my SQL knowledge to figure out the complete formula


Seeing you guys in action around here, I'm confident you'll have something to suggest on this subject


Thnx in forward !

 

[TonyGroves]

You could try the following:

SELECT *
FROM
my_table
WHERE
userid LIKE 'BRF-___-TLE'
AND MID(userid,5,3) BETWEEN '100' AND '200'

The 3 underscores in the LIKE argument will match any 3 characters.