mysql query and regexp problem

A few months ago i asked the question below.

My question.

However, i have ran into a problem.

When i use this query:

SELECT MAC, NAME FROM DB.HOST WHERE NAME REGEXP (SELECT CONCAT(LEFT(NAME, LENGTH(NAME)-1), "[0-9]+") FROM DB.HOST WHERE MAC="some mac");

If the mac address is resolved to "example_224-06-55" and their is another element in the DB named "example_224-06-55-00" they will both show up as a result of this query. I only want "example_224-06-55" to show up as a result of that query.

The size of the name's will vary, the examples are just examples.

I am having a really hard time figuring this out, any help is greatly appreciated!

THE WORKING QUERY:

SELECT MAC, NAME FROM DB.HOST WHERE NAME REGEXP (SELECT CONCAT(LEFT(NAME, LENGTH(NAME)-1),开发者_如何转开发 "[0-9][[:>:]]") FROM DB.HOST WHERE MAC="some mac");

MySQL supports a special word boundary pattern [[:>:]] to solve this.

SELECT MAC, NAME FROM DB.HOST 
WHERE NAME REGEXP (
 SELECT CONCAT(LEFT(NAME, LENGTH(NAME)-1), '[0-9]+[[:>:]]') 
 FROM DB.HOST 
 WHERE MAC='some mac'
);

See http://dev.mysql.com/doc/refman/5.1/en/regexp.html, the word boundary patterns are documented near the bottom of the page.

ps: Use single-quotes for string literals in SQL. Double-quotes are for delimited identifiers.

Update your regular expression that exactly you want.

SELECT MAC, NAME FROM DB.HOST 
 WHERE NAME REGEXP 
  (SELECT CONCAT(LEFT(NAME, LENGTH(NAME)-1), "_[0-9]+-[0-9]+-[0-9]+$") 
    FROM DB.HOST WHERE MAC="some mac");

If you want to write a pattern for strings ending with numbers like 'xxx-xx-xx' instead of "[0-9]+" you can use "\d\d\d-\d\d-\d\d"