select records where regex equals other column

Writing this question I got an idea for an anwser using concatenation.

SELECT * FROM myTable t WHERE columnB REGEXP CONCAT(t.columnA, '$');

You should fix your data model! Do not store numbers as strings! Do not store multiple values in a string column!

That said, sometimes we are stuck with other peoples really, really bad decisions. MySQL has a useful function, find_in_set() for this case:

where find_in_set(columnA, columnB) > 0

I am a little embarrassed to say I couldn't find a way to do this correctly with pure SQL. It seems completely obvious that I would want to use the data from one field as a regex in another, but there seems to be no straightforward way to do this if it is possible at all.

I had a similar problem with this question and chose a similar path. How can I store a regex pattern in a MySQL field and check an input against it?

I tried the other solutions and they seemed to work for simple cases, but I am not sure how they would scale. Also many of them had the problem mentioned above where 21 would also match 321 etc. This type of thing is trivial to solve correctly in Perl. The regex to match this is only...

$columnB =~ /,$columnA$/;

columnA has to be anchored at the end of columnB with the delimiter , in front. That way it won't mistakenly match substrings. So I decided to hand write a solution in Perl myself. The script first drops the table if it is there, creates the table, inserts the data, then runs the regex check on each row from the database using DBI.

Here is the code...

#!/usr/bin/perl -w

use DBI;
#pass all database information as command line argument
my ($database,$host,$port,$user,$pass) = (shift,shift,shift,shift, shift);
my $dsn = "DBI:mysql:database=$database;host=$host;port=$port";
my $dbh = DBI->connect($dsn,$user,$pass) or die "Connection Error: $DBI::errstr\n";

my $createTableSql = "
    CREATE TABLE `numberCheck` (
      `columnA` int(32) NOT NULL,
      `columnB` varchar(256) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

#I inserted a few test rows for debugging
my $insertRowsSql = "INSERT INTO `numberCheck` (`columnA`, `columnB`)
    VALUES
        (32,  '1,8,12,32'  ),
        (16,  '1,1,2,9,2,7'),
        (321, '3,10,56,111,321'),
        (21,  '3,10,56,111,321'),
        (1,   '2,1,7,4,1'),
        (2,   '2,1,3,5,8'),
        (3,   '2,5,7,7,3'),
        (4,   '4,4,2,6,7'),
        (5,   '1,5,3,2,5'),
        (6,   '1,1,3,4,3'),
        (7,   '2,6,7,1,7');
";

#drop table numberCheck first.  If it fails it's OK just go ahead and create it anyway
eval { $dbh->do("DROP TABLE numberCheck") }; print "Drop table failed: $@\n" if $@;
$dbh->do($createTableSql);
$dbh->do($insertRowsSql);

my @matchingRows;

# now retrieve data from the table.
my $sth = $dbh->prepare("SELECT * FROM numberCheck");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
  my $columnA = $ref->{columnA};
  my $columnB = $ref->{columnB};
  if($columnB =~ /,$columnA$/ ){
    print "MATCH: columnA: $ref->{columnA} columnB: $ref->{columnB}\n";
    push(@matchingRows,$ref->{columnA});
  } else{
    print "\tDID NOT MATCH: columnA: $ref->{columnA} columnB: $ref->{columnB}\n";
  }
}

my $matchingRowsSql = "select * from numberCheck where columnA in (" . join(',',@matchingRows) . ");";
print "\nQuery to get matching rows:\n$matchingRowsSql\n\n";

my $nonMatchingRowsSql = $matchingRowsSql =~ s/in/not in/r;
print "Query to get non matching rows:\n$nonMatchingRowsSql\n";

$sth->finish();

Seems to be working. Here is the output...

$ perl run.regex.on.column.with.data.from.another.column.pl DATABASE HOST PORT USER PASS

MATCH: columnA: 32 columnB: 1,8,12,32
    DID NOT MATCH: columnA: 16 columnB: 1,1,2,9,2,7
MATCH: columnA: 321 columnB: 3,10,56,111,321
    DID NOT MATCH: columnA: 21 columnB: 3,10,56,111,321
MATCH: columnA: 1 columnB: 2,1,7,4,1
    DID NOT MATCH: columnA: 2 columnB: 2,1,3,5,8
MATCH: columnA: 3 columnB: 2,5,7,7,3
    DID NOT MATCH: columnA: 4 columnB: 4,4,2,6,7
MATCH: columnA: 5 columnB: 1,5,3,2,5
    DID NOT MATCH: columnA: 6 columnB: 1,1,3,4,3
MATCH: columnA: 7 columnB: 2,6,7,1,7

Query to get matching rows:
select * from numberCheck where columnA in (32,321,1,3,5,7);

Query to get non matching rows:
select * from numberCheck where columnA not in (32,321,1,3,5,7);

I ran the resulting queries in mysqlsh and got the following result...

+---------+-----------------+
| columnA | columnB         |
+---------+-----------------+
|      32 | 1,8,12,32       |
|     321 | 3,10,56,111,321 |
|       1 | 2,1,7,4,1       |
|       3 | 2,5,7,7,3       |
|       5 | 1,5,3,2,5       |
|       7 | 2,6,7,1,7       |
+---------+-----------------+
+---------+-----------------+
| columnA | columnB         |
+---------+-----------------+
|      16 | 1,1,2,9,2,7     |
|      21 | 3,10,56,111,321 |
|       2 | 2,1,3,5,8       |
|       4 | 4,4,2,6,7       |
|       6 | 1,1,3,4,3       |
+---------+-----------------+

If I run across an easier way to do it I will update the answer. I saw some stuff using user-defined variables that looks promising, but I didn't get anything working that way yet.