How to search values that begin or end with another value that contains % and _ chars?

Question

Consider the following tables :

 drop table if exists testA;
 drop table if exists testB;
 create table testA ( id int, testX varchar(255) );
 create table testB ( id int, testY varchar(255) );
 insert into testA values ( 1, 'this is a test%' );
 insert into testB values ( 1, 'test%' );
 insert into testA values ( 1, 'a test% this is' );

This result in :

mysql>  select * from testA;
+------+-----------------+
| id   | testX           |
+------+-----------------+
|    1 | this is a test% |
|    1 | a test% this is |
+------+-----------------+
2 rows in set (0.04 sec)

mysql>  select * from testB;
+------+-------+
| id   | testY |
+------+-------+
|    1 | test% |
+------+-------+
1 row in set (0.05 sec)

How do I get values from the testA table that end with the testB value ?

This query :

SELECT tA.testX
FROM testA tA
JOIN testB tB USING (id)
WHERE tA.testX LIKE CONCAT('%', tB.testY);

Returns the whole results :

+-----------------+
| testX           |
+-----------------+
| this is a test% |
| a test% this is |
+-----------------+
2 rows in set (0.04 sec)

This sounds logic of course, because CONCAT('%', tB.testY) will return '%test%'. But in my app, I have complex values at the right side of LIKE, containing function calls and mix of columns and strings.

Answer 1

I finally found my solution :

SELECT tA.testX
FROM testA tA JOIN testB tB USING (id)
WHERE RIGHT(tA.testX, LENGTH(tB.testY)) = tB.testY

Give me :

+-----------------+
| testX           |
+-----------------+
| this is a test% |
+-----------------+

You can do it with :

• LEFT if you need a string that begin with a value (LIKE 'xxx%').
• RIGHT if you need to search a string that end with a value (LIKE '%xxx')
• LOCATE if you need to search a string that contains a value (LIKE '%xxx%')

I post it in case somebody will find that useful.