mysql using variables with regex

Question

how do i go about using Mysql variables inside... Mysql regex?

for example,

SET @my_var = (
SELECT 
year 
FROM blah
LIMIT 1);

SELECT 
foo,
bar
FROM my_table
WHERE date REGEXP @my_var%

also tried... WHERE date REGEXP '@my_var%'

what i'm trying to do is... WHERE date REGEXP '2015%'

Gordon Linoff · Accepted Answer · 2015-07-20 11:40:17Z

1

You would use concat:

where col regexp concat(@my_var, '%')

But two things:

So, I think you want:

where year(date) = 2015

or:

where date >= '2015-01-01' and date < '2016-01-01'

The last version is preferred because it can take advantage of an index, if available.

answered Jul 20, 2015 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cheers Gordon, that answered it... where year(date) = @current_year

Mack Baczynski · Accepted Answer · 2021-01-06 20:49:53Z

0

Another way, depending on how organized you want to be...

    SET @var = 101;
    SET @regex = concat("^",@var,"$");

    SELECT * FROM db.table 
    WHERE col REGEXP @regex;

answered Jan 6, 2021 at 20:49

Mack Baczynski

1