Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Combine SELECT and LOCATE to find strings within other strings

I’m building a very simple profanity filter in a chat app. I know they are notoriously difficult to implement properly, but I just want something quick and simple that is better than nothing at all.

I have a profanities table structured as follows:

|----|-----------|
| ID |   Word    |
|----|-----------|
| 01 | Banana    |
| 02 | Melon     |
| 03 | Eggplant  |
| 04 | Pineapple |
|----|-----------|

My basic query to find if a word a user has typed appears on the profanities table is pretty simple:

SELECT ID FROM profanities WHERE Word = '$word';

I can modify this to use LIKE or % etc., but that’s pretty straightforward.

What I also want is to combine this with a query that also checks if a word on the table is WITHIN the word the user typed. For example, the above query fails on words like "AllBananas" or even BananaMelon. I have tried some combinations of LOCATE() but they don’t work.

Is there a way to combine a query that checks if a passed word (or similar) is both ON the table, as well as if any word on the table appears WITHIN the passed word?

Tags:

2

Answers


  1. 0

    When you switch positions it will work

    You would also need a collation that is case insensitive

    Your code suggest that you don’t use prepared statements, that can lead to sql injection and cause problem, so it would be better to switch to prepared statements all the time.

    CREATE TABLE profanities  (
  `ID` INTEGER,
  `Word` varchar(50)
);

INSERT INTO profanities
  (`ID`, `Word`)
VALUES
  ('01', 'Banana'),
  ('02', 'Melon'),
  ('03', 'Eggplant'),
  ('04', 'Pineapple');

    Records: 4  Duplicates: 0  Warnings: 0

    SELECT ID FROM profanities WHERE   'AllBananas' LIKE CONCAT('%',Word,'%')  ;
    ID
    1 
    SELECT ID FROM profanities WHERE   'Allbananas' LIKE CONCAT('%',Word,'%')  ;
    ID
    1 
    SELECT ID FROM profanities WHERE   'BananaMelon' LIKE CONCAT('%',Word,'%')  ;
    ID
    1
    2

    fiddle

    Login or Signup to reply.
  2. 0

    You can get both tests in the same query – do two LOCATE() tests in Case expressions

    --  S a m pl e   D a t a :
CREATE TABLE tbl (ID CHAR(2),   TABLE_WORD VARCHAR(64));
INSERT INTO tbl VALUES
('01',  'Banana'),
('02',  'Melon'),
('03',  'Eggplant'),
('04',  'Pineapple');

    --  S Q L :
Select    t.ID, t.TABLE_WORD , 
          :USER_WORD as USER_WORD,
          --
          Case When LOCATE(Upper(:USER_WORD), Upper(t.TABLE_WORD)) > 0 Then 'Y' 
          Else 'N'
          End as USERWORD_FOUND_IN_TABLEWORD, 
          --
          Case When  LOCATE(Upper(t.TABLE_WORD), Upper(:USER_WORD)) > 0 Then 'Y' 
          Else 'N'
          End as TABLEWORD_FOUND_IN_USERWORD
From      tbl t

    --      R e s u l t s :
/*             for Pineapple:
ID  TABLE_WORD  USER_WORD   USERWORD_FOUND_IN_TABLEWORD TABLEWORD_FOUND_IN_USERWORD
--  ----------  ----------  --------------------------- ---------------------------
01  Banana      Pineapple   N                           N
02  Melon       Pineapple   N                           N
03  Eggplant    Pineapple   N                           N
04  Pineapple   Pineapple   Y                           Y                           */

/*             for apple:
ID  TABLE_WORD  USER_WORD   USERWORD_FOUND_IN_TABLEWORD TABLEWORD_FOUND_IN_USERWORD
--  ----------  ----------  --------------------------- ---------------------------
01  Banana      apple       N                           N
02  Melon       apple       N                           N
03  Eggplant    apple       N                           N
04  Pineapple   apple       Y                           N                           */

/*             for AllBananas:
ID  TABLE_WORD  USER_WORD   USERWORD_FOUND_IN_TABLEWORD TABLEWORD_FOUND_IN_USERWORD
--  ----------  ----------  --------------------------- ---------------------------
01  Banana      AllBananas  N                           Y
02  Melon       AllBananas  N                           N
03  Eggplant    AllBananas  N                           N
04  Pineapple   AllBananas  N                           N                           */

/*             for BAnANaMElOn  :
ID  TABLE_WORD  USER_WORD   USERWORD_FOUND_IN_TABLEWORD TABLEWORD_FOUND_IN_USERWORD
--  ----------  ----------- --------------------------- ---------------------------
01  Banana      BAnANaMElOn N                           Y
02  Melon       BAnANaMElOn N                           Y
03  Eggplant    BAnANaMElOn N                           N
04  Pineapple   BAnANaMElOn N                           N                           */

/*             for an:
ID  TABLE_WORD  USER_WORD   USERWORD_FOUND_IN_TABLEWORD TABLEWORD_FOUND_IN_USERWORD
--  ----------  ----------  --------------------------- ---------------------------
01  Banana      an          Y                           N
02  Melon       an          N                           N
03  Eggplant    an          Y                           N
04  Pineapple   an          N                           N                           */

    Additionaly – if you want to know if there is a match anywhere you can enclose Case expressions within GREATEST() function and get the results as below:

    --  S Q L :
Select    t.ID, t.TABLE_WORD , 
          'BAnANaMElOns with Pineapples' as USER_WORD,
GREATEST(Case When LOCATE(Upper('BAnANaMElOns with Pineapples'), Upper(t.TABLE_WORD)) > 0
               Then 'Y' 
          Else 'N'
          End, 
           Case When  LOCATE(Upper(t.TABLE_WORD), Upper('BAnANaMElOns with Pineapples')) > 0  
                Then 'Y' 
           Else 'N'
           End) as FOUND_ANYWHERE
From      tbl t;
/*
ID  TABLE_WORD  USER_WORD                       FOUND_ANYWHERE
--  ----------  ------------------------------  --------------    
01  Banana      BAnANaMElOns with Pineapples    Y
02  Melon       BAnANaMElOns with Pineapples    Y
03  Eggplant    BAnANaMElOns with Pineapples    N
04  Pineapple   BAnANaMElOns with Pineapples    Y               */

/*
ID  TABLE_WORD  USER_WORD                       FOUND_ANYWHERE
--  ----------  ------------------------------  --------------    
01  Banana      pl                              N
02  Melon       pl                              N
03  Eggplant    pl                              Y
04  Pineapple   pl                              Y               */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search