I have a table called blacklisted_usernames. These are usernames with wildcards in them that aren’t allowed to be registered onto my site.
create table blacklisted_usernames (
name varchar(64) not null
);
Some dummy data:
insert into blacklisted_usernames (name) values
('%admin%'),
('king%'),
('bad'),
('%cool');
The % indicates the same thing as the wildcard in the LIKE function in MySQL. I want to create an efficient case insensitive query which tells me if a username is blacklisted or not. For example is the username AdminJohn allowed? The answer would be no, because of %admin% being in the blacklisted_usernames table.
I understand I can do something like
SELECT 1
WHERE 'AdminJohn' LIKE '%admin%'
or 'AdminJohn' LIKE 'king%'
or 'AdminJohn' LIKE 'bad'
or 'AdminJohn' LIKE '%cool'
But I am manually typing out all the LIKE’s. I also don’t think it would be efficient if I created a loop checking it 1 by 1. Is there a way I can make this into an automatic but efficient way of checking against the names in blacklisted_usernames table and determining if a username is allowed?
>Solution :
select 1 from blacklisted_usernames where 'AdminJohn' like name limit 1