Kim Christensen wrote:
Is there any way to build a REGEXP pattern set by using a subquery?
I have a set of rows in table "table", with the column "value" being
target for my query. That column's content is a bracket separated list
of values, like this:
[118][Word][Integer][Stuff]...
[67][Another word][Another integer][More stuff]...
Now, to get all rows which "value string" starts with "[118]", this
does the trick:
SELECT * FROM table WHERE value REGEXP '^\\[118'
And further on, to get all rows which "value string" starts with
either [21], [42] or [999], this works fine:
SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)'
But I need to be able to do this last query without having to specify
the values by hand, but with a subquery. Imagine the following query:
SELECT id FROM items WHERE parent=5
This gives me a result set of rows which "parent" columns matches 5. I
would like to use all these results in the last REGEXP query example
above, something like this:
SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items
WHERE parent=5)'
Anyone got any clues?
--
Kim Christensen
[EMAIL PROTECTED]
Gleb has already sent a suggestion to do what you asked. I'd like to add that
the problem you're having is one perfect example of why this is not the best
table design. It is a bad idea to put multiple values, especially different
types of values, in one column. If you have control over this, you should
change your table so that each value is in its own column.
Change this ==> to something like this
============ ========
value iid word num stuff
---------------------------------- --- ------- --- ----------------
[118][Word1][6][Something] 118 'Word1' 6 'Something'
[67][Word2][12][Something else] 67 'Word2' 12 'Something else'
Then your query is simple:
SELECT * FROM table WHERE iid IN (SELECT id FROM items WHERE parent=5);
Better yet (probably faster), use a join:
SELECT * FROM table
JOIN items ON table.iid = items.id
WHERE items.parent=5;
A simpler query is not the only benefit. Now that your integers are actually
stored as integers instead of as strings, the query will run much faster,
because integer comparisons are an order of magnitude faster than string
comparisons.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
