Search code examples
mysqlsearchprefix

SQL query to search for longest common prefixes

In my database I have a table PRODUCT with a VARCHAR(255) column PRODNAME. In that column I store all my product names.


Now I've got to implement a simple search engine looking at that column. I'd like to use the LIKE statements as an exact match == over strings would be too restrictive but I want to match only prefixes inside the text of column PRODNAME. How to do so? Thanks!

Solution

  • You can use the wildcard (percent sign), ie.

    SELECT * FROM PRODUCT WHERE PRODNAME LIKE 'prefix%'

    To search for all substrings, use:

    SELECT * FROM PRODUCT WHERE PRODNAME LIKE '%substring%'

    Or suffixes:

    SELECT * FROM PRODUCT WHERE PRODNAME LIKE '%suffix'