How to select where LIKE format='%ATX%' but not Micro ATX or mATX

Question

I have a column that looks like this ATX, mATX (Micro ATX), mITX (Mini ITX), eATX (Extended ATX) I need to select it because it contains the first ATX but if it would look like this for example mATX (Micro ATX), mITX (Mini ITX), eATX (Extended ATX) then I don't want to select this row.

This is my SQL but it's reading also the words mATX, Micro ATX, eATX etc.

SELECT * FROM case_list WHERE dostupnost=1 AND format_dosky LIKE '%ATX%' LIMIT 32

How can I modify it so I can get it to work?

If you need to match substrings with exceptions, you're better off with [`REGEXP`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html) than `LIKE`. For example, `\bATX\b` for ATX without leading or trailing characters. — Markus AO, Dec 17 '22 at 13:51
Read this: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and then improve your understanding of relational database design, and amend your data structure to be properly normalised. Then you would not have this problem to begin with, and could just search for an exact value in the column. — ADyson, Dec 17 '22 at 13:55
Are you saying that the column must "start with 'ATX'? Or are you saying that one row has 'ATX' and the next row has 'mATX (Micro ATX)', etc? — Rick James, Dec 18 '22 at 07:09

score 0 · Answer 1 · answered Dec 17 '22 at 15:15

Here is an example of how to modify the query to make it workable with REGEXP:

SELECT * FROM case_list WHERE dostupnost=1 AND format_dosky REGEXP '\bATX\b' LIMIT 32;

The \b boundary matches match the beginning and end of a word, respectively. The REGEXP operator allows you to use regular expressions to match patterns in strings.

How to select where LIKE format='%ATX%' but not Micro ATX or mATX

1 Answers1