MySQL regular expression operations

Question

The set-up

A mysql table called "mytable" has a VARCHAR field "data".

The field contains a string in the format of:

name1'value1''name2'value2'' ... nameN''valueN

I'm aware this data is denormalized -- I've been forced to use ' as the delimited since this is the only character that is not allowed as a name or value.

Here are some example data:

one'.6332''two'.4231''three'.343''four'.034
two'.4332''four'.033''five'.043
four'.2323''seven'.3409''nine'.003

The problem

I'm trying to select rows from "mytable" based on a name/value pair. For example, I'd like to select all rows that in their data field, have a name of "four" and a corresponding value less than .1. (This should return rows 1 and 2).

I'd like to do this in one step. I realize I can denormalize the data and quite easily do it this way, but I'd prefer not to. This operation will only happen on, at most, 500 rows, so I'm not terribly concerned with its performance.

For inputs $name and $value, I assume the solution would look something like:

SELECT * FROM mytable WHERE 
  TO_INT(reg_ex(<crazyregex which selects $name>, data)) < $value

The problem is, I do not know the specifics of how to do this.

Hoping somebody can help. Thanks!

Answer 1

Ugly, but I think it works:

CREATE TABLE t (d varchar(255));

INSERT INTO t (d) VALUES
    ("one'.6332''two'.4231''three'.343''four'.034"),
    ("two'.4332''four'.033''five'.043"),
    ("four'.2323''seven'.3409''nine'.003"),
    ("four'.011''five'.043"),
    ("four'.100''seven'.3409''nine'.003")
;


SELECT
  d,
  convert(
    right(d, length(d) - instr(d, "four'")- 4),
    decimal(5,4)
  ) as v
FROM t
where d regexp "four'.0[0-9]*"
;