Mysql like to match pattern at end of string

Question

I have the following strings in the following pattern in a table in my db:

this_is_my_string_tester1
this_is_my_string_mystring2
this_is_my_string_greatstring

I am trying to match all strings that start with a specific pattern split by underscores i.e.this_is_my_string_ and then a wildcard final section

Unfortunately there is an added complication where some strings like the following:

this_is_my_string_tester1_yet_more_text
this_is_my_string_mystring2_more_text
this_is_my_string_greatstring_more

Therefore taking the following as examples:

this_is_my_string_tester1
this_is_my_string_mystring2
this_is_my_string_greatstring
this_is_my_string_tester1_yet_more_text
this_is_my_string_mystring2_more_text
this_is_my_string_greatstring_more

I am trying to have returned:

this_is_my_string_tester1
this_is_my_string_mystring2
this_is_my_string_greatstring

I have no idea how to do this with a like statement. Is this possible if so how?

EDIT

There is one final complication:

 this_is_my_string

needs to be supplied as a list i.e in

(this_is_my_string,  this_is_my_amazing_string, this_is_another_amazing_string)

Joop Eggen · Accepted Answer · 2013-10-14 12:19:30Z

1

SELECT * FROM atable WHERE afield REGEXP 'this_is_my_string_[a-z]+'

It might be faster if you have an index on afield and do

SELECT * FROM atable WHERE afield REGEXP 'this_is_my_string_[a-z]+'
                     AND afield LIKE 'this_is_my_string_%'

After edit of question:

Either

SELECT * FROM atable
WHERE afield REGEXP '(this_is_my_string|this_is_my_amazing_string)_[a-z]+'

or maybe you want something like having a table with the prefixes:

SELECT *
FROM atable AS t,
    prefixes AS p
WHERE afield REGEXP CONCAT(p.prefix, '_[a-z]+')

As by the reference documentation this should not be possible, as a pattern (string literal) is required. Give it a try nevertheless. There the answer of @KayNelson with LIKE (?) and INSTR might do instead of REGEXP.

edited Oct 14, 2013 at 12:19

answered Oct 14, 2013 at 12:05

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

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

1 Comment

Marty Wallace Over a year ago

Thanks! I didnt realise i can use regex in mysql! There is one further complication that i need to account for. I shall edit the question

Mad Dog Tannen · Accepted Answer · 2013-10-14 12:13:37Z

0

try this

SELECT * FROM db.table WHERE strings LIKE 'this_is_my_string_%' AND instr(Replace(strings,"this_is_my_string_",""),"_") = 0;

It checks if more _ occurs after replacing the standard "this_is_my_string_"

answered Oct 14, 2013 at 12:13

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Collectives™ on Stack Overflow

Mysql like to match pattern at end of string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related