/* 014 Regular Expression, (search character in database ) */ -- create table CREATE TABLE tOne( id INT NOT NULL UNIQUE, name VARCHAR(50) NOT NULL, age INT NOT NULL CHECK(age >= 18), gender VARCHAR(1) NOT NULL, phone VARCHAR(10) NOT NULL UNIQUE, city VARCHAR(15) NOT NULL DEFAULT 'Agra' ); -- insert records INSERT INTO tOne(id,name,age,gender,phone,city) VALUES (1,"Ram Kumar","23","M","4022155","Agra"), (2,"Sarita Kumari","21","F","4034421","Delhi"), (3,"Salman Khan","20","M","4056221","Agra"), (4,"Juhi Chawla","28","F","4022156","Bhopal"), (5,"Anil Kapoor","22","M","4025221","Agra"), (6,"John Abraham","21","M","4056776","Delhi"); -- Regular Expression SELECT * FROM tOne WHERE name REGEXP 'ra'; -- return the world which contain ra,middle last or start SELECT * FROM tOne WHERE name REGEXP 'ta'; -- like above SELECT * FROM tOne WHERE name REGEXP 'man'; -- like above SELECT * FROM tOne WHERE name REGEXP '^ra'; -- starting form ra SELECT * FROM tOne WHERE name REGEXP '^sa'; -- starting from sa SELECT * FROM tOne WHERE name REGEXP 'an$'; -- end with an word SELECT * FROM tOne WHERE name REGEXP 'ram|kapoor|khan'; -- multiple search SELECT * FROM tOne WHERE name REGEXP 'ram|poor|khan'; -- same as above SELECT * FROM tOne WHERE name REGEXP '^ram|poor|^khan'; -- starting with ram, and poor word, and starting with khan SELECT * FROM tOne WHERE name REGEXP '^ram|poor|khan$'; -- check khan at the end SELECT * FROM tOne WHERE name REGEXP '[is]'; -- check each value SELECT * FROM tOne WHERE name REGEXP '[rm]'; -- same above SELECT * FROM tOne WHERE name REGEXP '[rm]a'; -- combination of ra and ma SELECT * FROM tOne WHERE name REGEXP '[rmh]a'; -- same as above SELECT * FROM tOne WHERE name REGEXP '^[rs]'; -- start with r or s SELECT * FROM tOne WHERE name REGEXP '[rs]$'; -- end with r or s SELECT * FROM tOne WHERE name REGEXP '[ra]$'; -- same as above SELECT * FROM tOne WHERE name REGEXP 'r[am]'; -- combination ra and rm SELECT * FROM tOne WHERE name REGEXP '[a-j]r'; -- range with r end