mysql query and regexp problem

Question

A few months ago i asked the question below.

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");

Bill Karwin · Accepted Answer · 2011-07-26 17:49:29Z

2

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.

answered Jul 26, 2011 at 17:49

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prolink007 Over a year ago

Thank you very much sir, that worked great! However, for it to work, i did have to remove the "+".

lqez · Accepted Answer · 2011-07-26 17:48:43Z

2

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");

answered Jul 26, 2011 at 17:48

lqez

3,0585 gold badges28 silver badges55 bronze badges

1 Comment

prolink007 Over a year ago

It can be of a varrying length, that was just an example.

Grigor Gevorgyan · Accepted Answer · 2011-07-26 17:45:21Z

1

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"

answered Jul 26, 2011 at 17:45

Grigor Gevorgyan

6,8934 gold badges38 silver badges64 bronze badges

4 Comments

prolink007 Over a year ago

The size can be different every time. It might not always be only 7 digits, it might be 9 next time, or 5.

Grigor Gevorgyan Over a year ago

Then please clarify why the string "example_224-06-55-00" does not satisfy your requirements. Is it about the trailing zeroes ?

prolink007 Over a year ago

Because if the "some mac" is resolved to a name "example_224-06-55" and their exists another name in the DB "example_224-06-55-00" they will both show up. I only want "example_224-06-55" to show up.

Grigor Gevorgyan Over a year ago

Thanks, just didn't get what the exact problem was at once :)

Collectives™ on Stack Overflow

mysql query and regexp problem

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related