Jump to content

Archived

This topic is now archived and is closed to further replies.

CaptainC

REGEXP in an sql query

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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' ".

Share this post


Link to post
Share on other sites
CONCAT takes as many arguments as you want.

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Where it discusses the REGEXP fucntion there is a link to another section of the manual just for regular expressions.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.