[SOLVED] Using REGEXP on SELECT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

[SOLVED] Using REGEXP on SELECT

Post by Jaxolotl »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi everyone!
I'm trying to improve my MySQL knowledge  using REGEXP on QUERIES, but I'm trying to make a query like this and I don't know how to implement the REGEXP

EXPL query
[syntax="sql"]
SELECT *
FROM `table`
WHERE `field` REGEXP 'my regexp'
the idea is to select everithing from TABLE that contains a number not preceded by or followed by a number.
my "filed" contains a string linke this:
row_1 = 12|500|1|1254|9|2
row_2 = 5|312|4|5412|99|20
and so on
a nuber sequence divided by |.
I want to match the rows that match for example "12" nad just "12" not "312" or "120".

Anyone can help me with this?
TNX you in advance

Jaxolotl


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Last edited by Jaxolotl on Tue Nov 14, 2006 3:34 am, edited 1 time in total.
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

[SOLUTION] Using REGEXP on SELECT

Post by Jaxolotl »

Sorry guys, about the syntax, I'll try to do it wright.
And sorry about this post, I found the solution before post it, but I didn't really understand it 'till now. I was really tired yesterday, it didn't matter what I read, nothing was getting understandable for my ruined brain. (by the way my english is not such a WOW staff.....LOL)
Let's go on, the solution is on the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/regexp.html, I test it also on MySQL 4.1.9 and it works.
FROM MANUAL
-------------------
[[:<:]]STRING[[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Example:

Code: Select all

/*
TABLE sample_name
+----+----------------------------------+-----------------------+
| ID |            peak_point            |      sample_name      |
+----+----------------------------------+-----------------------+
| 1  |12-50-1243-120-3126-15-3-2-33     |sample 4c              |
+----+----------------------------------+-----------------------+
| 2  |14-12-33-55-49-2-312-1200         |sample dom             |
+----+----------------------------------+-----------------------+
| 3  |17-33-50-3612-25-15               |sample 22              |
+----+----------------------------------+-----------------------+
| 4  |18-31-57-3335-19-70               |sample 28              |
+----+----------------------------------+-----------------------+
*/

mysql > SELECT `sample_name` FROM `sample_table` WHERE `peak_point` REGEXP '[[:<:]]12[[:>:]]'
/* Result -> "sample 4c","sample dom"*/

mysql > SELECT `sample_name` FROM `sample_table` WHERE `peak_point` REGEXP '[[:<:]]33[[:>:]]'
/* Result -> "sample 4c","sample dom","sample 22"*/

mysql > SELECT `sample_name` FROM `sample_table` WHERE `peak_point` REGEXP '[[:<:]]3[[:>:]]'
/* Result -> "sample 4c"*/

I Hope it will be usefull for someone, (and I hope my post is correctly written this time, sorry again feyd)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

The disadvantage of regexp in a query is that a full-table scan is required... Whereas with a more normalized model (column has only one value, not a custom built group of values) you could take advantage of eventual indices...
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

Post by Jaxolotl »

Hi timvw, you're right 'bout that, and that's the way I use to store and retrive info from my db, but I like to try different ways to do the same thing, just to study, and ...may be........ and I say may be, this process would be usefull to retrive filtered data from a table with unknown amount of information but a well known number of record, for example a table with not more than 2000 records, but for example "the example I wrote before" an Unknown number of peaks for record, this is an exercise to store the most significant peaks (on the Spectrum Analysis) on a set of audio samples used to generate wave model. In this case I can't make a table with a huge number of columns just to assign a value to an specific frequency, i prefere to use a colum for the most important peaks in frequence and a column for the values in decibels.
By doing this I just don't need to know in advance how many coumns I need to save, just split the info

Code: Select all

+----------------------+------------------------+
|        Hz            |       decibels         |
+----------------------+------------------------+
|200-500-1358          |30-15-5                 |
+----------------------+------------------------+
the other way could be to use 2 tables, one for the sample and one for the values like this

Code: Select all

-- Create the sample names with an indexed id
-- --------------------------------------------------------

CREATE TABLE `sample_index` (
`s_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`s_name` VARCHAR( 255 ) NOT NULL ,
`s_date` DATETIME NOT NULL ,
PRIMARY KEY ( `s_id` ) ,
INDEX ( `s_date` )
);

-- --------------------------------------------------------
-- then create atable for the data with 4 indexes
-- 1 - the id
-- 2 - the parent sample as a foreing index referenced to the sample_index table
-- 3 - the hz index
-- 4 - the db index

-- --------------------------------------------------------

CREATE TABLE `sample_data` (
`d_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`d_parent` INT UNSIGNED NULL ,
`d_hz` DECIMAL( 12, 4 ) NOT NULL ,
`d_decibels` DECIMAL( 12, 4 ) NOT NULL ,
PRIMARY KEY ( `d_id` ) ,
INDEX ( `d_parent`),
INDEX ( `d_hz` ),
INDEX ( `d_decibels` )
)

-- --------------------------------------------------------

-- then if for example I would like to know the name of the samples that match a significant
--  peak on the 20Hz I'd make a query like this

-- --------------------------------------------------------

SELECT `s_id`,`s_name` FROM `sample_index` WHERE `s_id` IN (SELECT `d_parent` FROM `sample_data` WHERE `d_hz` ='20')

do you think this is better for small record's db?
please give me your opinion timvw.

by tnx for your answers guys :)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

looks good :D
Post Reply