REGEXP question

[ninedoors]

Ok I have tried everything I can think of and I still cant get the result I would like.  I am trying to pull out a specific string but I keep getting extra matches that I don't want.  Here is the REGEXP query I am using:

 

SELECT *  FROM `ppk` WHERE `location` REGEXP '^PPK[[:digit:]]{0,1}'

 

What I want is the string of PPK followed by exactly one digit and thats it.  Instead I am getting things like PPK10, PPK101, PPK62.

 

What am I doing wrong?  Thanks for the help.

 

Nick

[fullyscintilla]

^PPK[0-9]{0,1}$

  ?

[fullyscintilla]

i'm taking a guess and saying that the script is continuing past just 1 digit because you didn't add the ending tag that says nothing else after this.  "$"

 

I could be wrong.

[ninedoors]

I tried that and I get an empty result.

[ninedoors]

I tried this too and got the same result giving me everything.

 

SELECT *  FROM `ppk` WHERE `location` REGEXP '^PPK[0-9]{1}'

 

And then this gives me nothing,

 

SELECT *  FROM `ppk` WHERE `location` REGEXP '^PPK[0-9]{1}$'

 

Not quite sure what I am doing wrong.  The database contains strings like PPK1, PPK2, PPK3

[fullyscintilla]

what if you tried this

 

SELECT *  FROM `ppk` WHERE `location` LIKE 'PPK_'

[ninedoors]

Ya thats what I tried first as I am just learning REGEXP, but I get an empty set for that as well.  And the crazy thing is when I try this:

 

SELECT *  FROM `ppk` WHERE `location` LIKE 'PPK?'

 

I still get an empty result.  Not sure what is going on.

[fullyscintilla]

Ya thats what I tried first as I am just learning REGEXP, but I get an empty set for that as well.  And the crazy thing is when I try this:

 

SELECT *  FROM `ppk` WHERE `location` LIKE 'PPK?'

 

I still get an empty result.  Not sure what is going on.

 

 

I don't know then man.. maybe the  problem isn't your query...

 

only other thing i can suggest is to do the regex with php or watever scripting language you might also be using.

[fullyscintilla]

i made a quick recreation of your db and tried the query.. it seems to work just fine and dandy for me...

 

I have 3 values in the DB

 

PPK1

PPK10

PPK3

 

when I run my query only PPK1 and PPK3 are returned.

 

what DB are you using... MySql here.

 

The query used:

SELECT * FROM ppk WHERE location LIKE 'PPK_'

[ninedoors]

Yes mysql, and the data is pulled from an SQL Server 2005 DB.  I was going to try to use PHP to do the regex but I know less about that than I do the mysql regexp.  So it looks as if I have to learn something new.....ah well, better in the long run I guess.

 

***heads to google to learn about regex in php***

[fullyscintilla]

Yes mysql, and the data is pulled from an SQL Server 2005 DB.  I was going to try to use PHP to do the regex but I know less about that than I do the mysql regexp.  So it looks as if I have to learn something new.....ah well, better in the long run I guess.

 

***heads to google to learn about regex in php***

 

is that MYSQL or MSSQL?

 

those are two different types of databases

[fullyscintilla]

I believe this is should work in php..

 

<?php

$SQL = "SELECT * FROM ppk";

$Result = mysql_query($SQL) or die("ERROR:".mysql_error());

while($Row = mysql_fetch_array($Result))
{
$Patt = '^PPK[0-9]{1}$';

if(preg_match($Patt,$Row['location'])
{
	echo("Match!: ".$Row['location']);
}
else
{
	echo("Not Match!: ".$Row['location']);
}
}

?>

[fenway]

you need ppk%, not ppk_ -- the latter matches only one character.

 

And an anchor, too....