REGEXP in an sql query

[CaptainC]

I am trying to incorporate REGEXP in an sql query (MySQL 4.3.x). The shortened database is as follows:

table1: t1_id, ids_in_table2
table2: t2_id, t2_data

In table1, the field 'ids_in_table2' represents a string of id numbers (integers only) separated by a space. For example "12 83 97 199". Those numbers are references to the field 't2_id' in table2. I want to obtain all the instances where 't2_id' appears in 'ids_in_table2' (my real example is more elaborate, but I simplified it to focus on what is important). My best query so far is:

[code]SELECT table1.ids_in_table2, table2.t2_id FROM table1, table2 WHERE table1.ids_in_table2 REGEXP '\s?'+table2.t2_id+'\s?'[/code]

This is returning a partially correct list. For example, it wille fail when it returns values where ids_in_table2 = "15 16" and t2_id = "5". It will return this set of data because it found the 5 in the string from ids_in_table2.

I would appreciate any help especially concerning how I am using the REGEXP keyword in mysql and the "+" operator as string catenation. I cannot find sufficient documentation and examples of this keyword.

[fenway]

Yikes.

First, there is no version 4.3.x.
Second, there is no "\s" -- you have to use "[:space:]" instead.
Third, you'll need to use CONCAT() to join the strings.
Last, this the _wrong_ way to store IDs!!! Make a lookup table instead.

[CaptainC]

Thanks for the response. I was guessing at the sql version, its 4.x, i just didnt look up the exact number yet as I knew the functions were included in all 4.x releases. Also, I am working with a database that already exists, otherwise, I would have created the tables differently.

Otherwise, can CONCAT() take multiple (>2) parameters? Finally, how can I make sure that it does not match a "5" to a "15"? There are optional spaces surrounding a number, but I also want to specify: "no number before or after 'table2.t2_id' ".

[wickning1]

CONCAT takes as many arguments as you want.

Instead of making the space optional, you can do CONCAT('([:space:]|^)', table2.t2_id, '([:space:]|$)')

[CaptainC]

That was perfect. Thank you very much. Do you know of any good sites for documentation/tutorials on MySQL REGEXP syntax? dev.mysql.com has references to the keyword, but its not very extensive at all especially with the formatting of the regular expression.

[wickning1]

Where it discusses the REGEXP fucntion there is a link to another section of the manual just for regular expressions.

[CaptainC]

Ok found it. Just in case others need it, the location of the mysql regexp reference: [a href=\"http://dev.mysql.com/doc/refman/4.1/en/regexp.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/regexp.html[/a]

Thank again.