Creating REGEXP patterns with use of subquery
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating REGEXP patterns with use of subquery
Hello. This looks like a task for prepared statements. You can dynamically form the string from the query using GROUP_CONCAT, assign the created string to the variable. Then just prepare a right statement and invoke it using that variable. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html 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] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating REGEXP patterns with use of subquery
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]
