Jump to content


Photo

REGEXP in an sql query


  • Please log in to reply
6 replies to this topic

#1 CaptainC

CaptainC
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 26 March 2006 - 01:10 AM

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:

SELECT table1.ids_in_table2, table2.t2_id FROM table1, table2 WHERE table1.ids_in_table2 REGEXP '\s?'+table2.t2_id+'\s?'

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 March 2006 - 04:48 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 CaptainC

CaptainC
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 26 March 2006 - 10:42 AM

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' ".

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 26 March 2006 - 03:11 PM

CONCAT takes as many arguments as you want.

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

#5 CaptainC

CaptainC
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 26 March 2006 - 03:43 PM

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.

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 26 March 2006 - 06:39 PM

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

#7 CaptainC

CaptainC
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 26 March 2006 - 10:23 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users