CODEWITHSUNDEEP
mysql
Marty Wallace
Marty Wallace

Reputation: 35796

Mysql like to match pattern at end of string

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)

Upvotes: 0

Views: 1852

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109613

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.

Upvotes: 1

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

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_"

Upvotes: 0

Related Questions