Welcome to WebmasterWorld Guest from 54.196.214.35

Forum Moderators: open

Message Too Old, No Replies

how to compare two instances using MySQL REGEXP Function

how to compare two instances using MySQL REGEXP Function

     
5:46 pm on Dec 22, 2005 (gmt 0)

New User

10+ Year Member

joined:July 20, 2005
posts:28
votes: 0


hi,
can somebody tell me what m i doing wrong in this MySQL statement.
i want to compare '/' at the start and end of the string in my Link field which can have any chracters from 'a-z' and '-' and it cant have '/' again in the middle of string, just at start and end. i ve tried the following statement and other variations of it but to no avail.


SELECT Name, Link FROM Table WHERE Link REGEXP '^/[a-z-]/$'

please help.

10:51 pm on Dec 22, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


Are you trying to find one or more instances of those characters? Use the plus sign. Use the asterisk for zero or more.
SELECT Name, Link FROM Table WHERE Link REGEXP '^/[a-z-]+/$';
3:27 am on Dec 23, 2005 (gmt 0)

New User

10+ Year Member

joined:July 20, 2005
posts:28
votes: 0


exactly & that worked like a charm. :D
on another note, what if we want to exclude '/' from the string,
for instance, i ve records '/category/' or '/detailed-name-category/' which i want to match but i also have such records like '/category/subcategory/' or '/detailed-name-category/detailed-name-subcategoery/' which i dont want to match, and in your provided statement they dont, is this statment
SELECT Name, Link FROM Table WHERE Link REGEXP '^/[a-z-]+/$';

explicitly exclude '/' from the string apart from start and end so that only categories match and not the subcat or any other records.
but this statement does what i want to do in anycase, i m just trying to dig a bit deeper.
thnx
7:45 am on Dec 23, 2005 (gmt 0)

New User

10+ Year Member

joined:July 20, 2005
posts:28
votes: 0


ok , got it now completely,

SELECT Name, Link FROM Table WHERE Link REGEXP '^/[a-z-]+/$'; 

in the aboive staement, we are explicitly defining which chracters to match [a-z-]+ and it skipped '/' in the middle of string.
thnx.

3:12 pm on Dec 23, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


Exactly. Since the slash character (/) is not represented in the character class (the *stuff* in between the brackets ([]) ) then it is not a valid matching character. And the caret (^) and dollar sign are anchors, they say that the pattern must begin with the anchor specified as well as end with the anchor specified -- in this case it is the slash character.