MySQL Select Where/Like

Question

I need help with a WHERE/LIKE clause inside a select statement. I want it to return values from table1.results WHERE/LIKE the table2.selection column contains all the characters from the table1.data column.

If the selection column had the characters “abc” and the data column had “bc” than it could be a match. If the selection column had “abc” and the data column had “abcd” than it wouldn’t be a match as the selection column was missing “d”.

I’ve tried different operators and regexp and can’t figure it out.

Table1:

wdt_id | data | results
1 | abcd | Mazda
2 | abde | Toyota
3 | ace | Honda

Table2:

wdt_id | selection | selection_results
1 | abde | null
2 | abcd | null
3 | ab | null

Statement:

SELECT results
FROM table1
WHERE table1.data LIKE concat(‘%’,table2.selection,'%');

Edit: I’m added some data above. The select statement is part of a large UPDATE Trigger. The trigger updates the selection_results column. With the above data the selection result for row1 would be Toyota.

can you add some data, so that we can understand better what you are doing we need a [mre] — nbk, Jun 19 '21 at 14:01
Thanks! Sorry for the delay. Took me a while to test inside my update trigger. — John_Scully, Jun 19 '21 at 15:32

nbk · Accepted Answer · 2021-06-19T15:08:39.730

0

With your few sample data it should look like

As this can potentionally have more than one result, your the SELECT nees a LIMIT 1 and an ORDER BY, that you will have to check

CREATE TABLE Tab1 (wdt_id int, data varchar(10), results vARCHAR(20))

INSERT INTO Tab1
  (`wdt_id`, `data`, `results`)
VALUES
  ('1', 'abcd', 'Mazda'),
  ('2', 'abde', 'Toyota'),
  ('3', 'ace', 'Honda');

CREATE TABLE Tab2 (wdt_id int , selection varchar(20),selection_results varchar(20))

INSERT INTO Tab2
  (`wdt_id`, `selection`, `selection_results`)
VALUES
  ('1', 'abde', null),
  ('2', 'abcd', null),
  ('3', 'ab', null);

SELECT results FROM Tab1 WHERE EXISTS ( SELECT 1 FROM Tab2 WHERE selection LIKE CONCAT('%', Tab1.data , '%'))

| results |
| :------ |
| Mazda   |
| Toyota  |

UPDATE Tab2 SET `selection_results`  = (SELECT results FROM Tab1 WHERE  Tab2.selection LIKE CONCAT('%', Tab1.data , '%') ORDER BY wdt_id LIMIT 1)

SELECT * FROM Tab2

wdt_id | selection | selection_results
-----: | :-------- | :----------------
     1 | abde      | Toyota           
     2 | abcd      | Mazda            
     3 | ab        | null

db<>fiddle here

edited Jun 19 '21 at 15:08

answered Jun 19 '21 at 14:17

nbk

45,398
8
30
47

Turns out I jumped the gun. After more testing I realized it still only works for exact string matches, not just the characters. Any idea what needs to change? – John_Scully Jun 25 '21 at 20:20
it does exactly what you asked for https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=227f2d34843b57b403b2ac22414f1fa8 – nbk Jun 25 '21 at 20:33
I’ll test some more! Thanks. – John_Scully Jun 25 '21 at 20:38
I’ve tested extensively. If Tab2 id 1 has “abcde” it should be able to return any of the results (Mazda,Toyota, Honda) but it will only return Mazda as it’s matching the string “abc”. I’ve rearranged the characters in both tables many times and it’s matching by string, not the individual characters regardless of order. – John_Scully Jun 26 '21 at 12:02
that is not a word comparison like you do in sql you want to check every occurance , that you will have to program in a function and is not that what you ask for. – nbk Jun 26 '21 at 12:15
Ok, thanks. I’ll mark this as answered. How would I phrase a new question with this functionality in mind? – John_Scully Jun 26 '21 at 13:40
there are aleready similiar questions like https://stackoverflow.com/questions/37213789/split-a-string-and-loop-through-values-in-mysql-procedure but they cover always only coma or semicolon separated strings, but the system works identical instead substring_index you only use substring. and run over the string length. so try your luck and when you are stuck explain it – nbk Jun 26 '21 at 14:01

MySQL Select Where/Like

1 Answers1