Match exact pattern

Question

I have a MySQL table that contains books information. Part of that information is a special character (^) separated list of values that corresponds to the book names. I'm having some problems getting the correct information out of the database. Table looks like

id    OwnedBooks                   CustomerID
1     Harry^Two States^Tintin         101
2     Harry Potter^Tintin             290
3     Harry Prizoner of Azhaban       278

So when I search for 'Harry' in the ownedbooks column, I should get only one record i.e. (record id=1)

My query looks like below

SELECT * FROM books where ownedbooks like '%Harry%'

This query return all the records as I have used like, but I wanted to match the exact string with (^) as a separation.

When I search for 'Harry Potter' it should return the second record i.e (record id=2)

Your problem is storing multiple values in one column. That's awful design, I'm afraid. But I don't see why `like '%Harry Potter%'` would not work for the case you requested. What happens when you run that? Why is it wrong? It's unclear what problem you're really having. — underscore_d, May 05 '21 at 10:10
The issue here is when i search for 'Harry' it returns all the records, but i wanted to return only matching record. — Muthu, May 05 '21 at 10:12
Of course a search for `%Harry%` finds _all_ your records here. `%` means _anything_, and even _nothing_. You would have to search for matches that _include_ the separator character here, `%^Harry^%`. You can not just add it on one side, `%Harry^%` would still find a record containing `Another Harry^…`, and since your column content does not contain `^` at the very start or end, you would have to add those around the value, _before_ you look for a match, as well … Do yourself the favor and properly normalize this now. — CBroe, May 05 '21 at 10:15
_The issue here is when i search for 'Harry' it returns all the records_ Thats NOT an issue, that is the **intended purpose of a LIKE** — RiggsFolly, May 05 '21 at 10:16

score 1 · Answer 1 · answered May 05 '21 at 10:12

If you want to match a book named exactly "Harry" then you may use the following LIKE logic:

SELECT *
FROM books
WHERE ownedbooks LIKE '%^Harry^%' OR  -- appears in the middle of the list
      ownedbooks LIKE 'Harry^%' OR    -- appears in the beginning of the list
      ownedbooks LIKE '^Harry';       -- appears in the end of the list

You would be making a good design decision to move away from storing ^ delimited data in your table. Instead, get each book title onto a separate record.

Match exact pattern

1 Answers1