...

View Full Version : regexp in mysql queries



LJackson
09-14-2011, 05:22 PM
Hi All

im trying to put together a query string but running some checks and depending on the result of these check adding to the query.

but my final query im trying to acheive is incorrect and i cant try and construct a broken query :(

here is my query

SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE description REGEXP '[[:<:]]$i[[:>:]]

what im trying to acheive is to search my description column to find the records that have include the value of $i in them

now the 4 values $i can be are:
"%","","delivery","warranty"

i need to be able to find the records containing any of these four values but when i try and test my final query its incorrect

like so

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[[:<:]]delivery[[:>:]]' at line 1

any ideas where i've gone wrong???

p.s any other examples not using regexp would be appreciated :)

thanks
Luke

LJackson
09-14-2011, 06:07 PM
ok got that sorted, needed to use this regexp

REGEXP '[[:<:]]($search)[[:>:]]'

does anyone know how i can search for records where details data does NOT include any of these values ("","%","delivery","warranty")

i assume i need regexp again but im not sure where to start :(

thanks
Luke

LJackson
09-14-2011, 06:35 PM
ok a strange happening,

i have this code

if($codeType <> "misc")
{
$start = "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE ";
if($codeType <> '')
{
$step1 = "details REGEXP '[[:<:]]($search)[[:>:]]'";
}
if($storeLetter <> '')
{
$step2 = "UPPER( SUBSTRING( stores.storeName, 1, 1 ) = '$storeLetter'";
}
$query = $start.$step1.$step2;
}
else
{
$start = "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID";
$query = $start;
}

echo $query;
while ($row = mysql_fetch_array($query))
{
...



which produces this output

SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE details REGEXP '[[:<:]](warranty)[[:>:]]'

but it says

Warning: mysql_fetch_array() expects parameter 1 to be resource, string given

but when running this code in my dbadmin it wrks fine? any reason as to why this fails in my php script, is it the way ive constructed it?

edit
===

ok i missed out the mysql_query part grrr am making some basic errors tonight :(

so to confirm i still need help with

1/ using and % with in my regexp it doesnt like then, what do i need to do to allow them to be used?
2/ how do i use regexp or any other mysql function to check for records where the details field does Not include any of the following "","%","delivery","warranty" strings

thanks

Old Pedant
09-14-2011, 08:45 PM
Guessing on first part:

Just put \ in front of and %

You'll probably need to do this in the PHP code:


if ( $search == "" || $search == "%" ) $search = "\\" . $search;


And did you try simply doing

$step1 = "details NOT REGEXP '[[:<:]]($search)[[:>:]]'";

???
http://dev.mysql.com/doc/refman/5.5/en/regexp.html#operator_regexp

It's right there as the first syntax choice, actually. <grin/>

LJackson
09-14-2011, 09:58 PM
hi mate
here is my updated code:

<?php
switch($codeType)
{
case "percentage":
$search = "\\%";
break;
case "money":
$search = "\\";
break;
case "delivery":
$search = "delivery";
break;
case "warranty":
$search = "warranty";
break;
case "miscellaneous":
break;
}

mysql_query("SET NAMES 'utf8'");
if($codeType <> "misc")
{
$start = "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE ";
if($codeType <> '')
{
$step1 = "code.details REGEXP '[[:<:]]($search)[[:>:]]'";
}
if($storeLetter <> '')
{
$step2 = "UPPER( SUBSTRING( stores.storeName, 1, 1 ) = '$storeLetter'";
}
$test = $start.$step1.$step2;
}
else
{
$start = "SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID";
$test = $start;
}
$query = mysql_query($test);

echo $test;


ive added the "\\" to the $search varible, its not returning any errors but its also not finding any records even tho there are some.

even in myphp admin it doesnt find any, ive even tried:
code.details REGEXP '[[:<:]]()[[:>:]]'";
code.details REGEXP '[[:<:]](\)[[:>:]]'";
code.details REGEXP '[[:<:]](\\)[[:>:]]'";

and neither return any results?


Guessing on first part:
And did you try simply doing

$step1 = "details NOT REGEXP '[[:<:]]($search)[[:>:]]'";



i need the expression to find fields which dont include any of the strings ("","%","delivery","warranty") not just one or another it has to be all, unfortunately :)

many thanks
Luke

Old Pedant
09-14-2011, 11:04 PM
??? So why are you using REGEXP? Won't LIKE work just as well?

As for : Again, just guessing, but I'd bet that you need to either use a UTF8 escape sequence or define the character set in use as UTF8.

Anyway...



WHERE details NOT LIKE '%%'
AND details NOT LIKE '%\%%'
AND details NOT LIKE '%delivery%'
AND details NOT LIKE '%warranty%'


You might need to use COLLATE for the test.


WHERE details NOT LIKE '%%' COLLATE latin1_general_ci

Not sure that's the right COLLATE value, but I think it should be (it's for general western European).

LJackson
09-14-2011, 11:08 PM
possibly... but like wont match just the strings they will match then if they are part of a larger string ie would match delivery's for example. no there are not many examples where this could happen so using like might just work :) but thats the reason i chose to go down the regexp route....

will give it a shot to see if i get any unexpected results :)

Old Pedant
09-14-2011, 11:27 PM
OH! Then it's even *EASIER*!!!


WHERE details NOT IN('','%','delivery','warranty')

And that's also extremely fast. MUCH faster than REGEXP would be. Plus, it *WILL* use the index if details is indexed. Which LIKE and REGEXP will not do.

LJackson
09-14-2011, 11:34 PM
ok that seems to return te same results as the regexp as expected which is good but like you thought

NOT LIKE '%%' isnt liked and doesnt work, ive added the COLLATE but didnt work :( any other ideas



p.s
WHERE details NOT IN('','%','delivery','warranty')

doesnt seem to work it returns rows witch have any of the above strings in?

LJackson
09-15-2011, 12:08 AM
have use this for the time being, seems to work :)

SELECT * FROM tbl_codes as code LEFT JOIN(stores as store) on code.storeID = store.storeID WHERE code.details NOT LIKE '%&pound;%' && code.details NOT LIKE '%\%%' && code.details NOT LIKE '%delivery%' && code.details NOT LIKE '%warranty%'

bit long winded but hay ho :)

Old Pedant
09-15-2011, 12:50 AM
???

I don't see how


WHERE details NOT IN('','%','delivery','warranty')

can't work if you are trying to eliminate records where details is exactly and only one of those. But I guess that's not what you meant. Ah, well.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum