REgexp use in search

--1 -> Search users who play either football OR baseball AND cricket
--2 -> Search users who have interest in music
--3 -> Print the location information and their hobbies, for all the users


-- Declare a cursor that fetches users with hobbies related either to football or baseball and cricket.
-- Here (basefoot)([[:space:]]){0,}ball([^.]*)cricket expression returns true if the 'hobbies' column contains
-- values like football,baseball, base ball, foot ball etc. ([^.]*) means occurence of any character multiple
-- times. The 'i' indicates case-insensitive search


SELECT username,hobbies
FROM userinfo
WHERE REGEXP_LIKE(hobbies,'(basefoot)([[:space:]]){0,}ball([^.]*)cricket','i');


-- Declare a cursor to fetch the user data if their hobbies are related to music.
-- The 'musicviolinguitarsing(ering)' pattern returns true if the given
-- data matches any of music,violin, guitar, singer, singing character patterns.
-- sing(ering) represents either singer or singing character pattern.


SELECT username,hobbies
FROM userinfo
WHERE REGEXP_LIKE(hobbies,'musicviolinguitarsing(ering)','i');


-- This retrieves the location of all the users in the database.
-- REGEXP_SUBSTR(WEBSITE, ',[^,]+',1,2) fetches the second substring bounded by commas


SELECT username, hobbies, REGEXP_SUBSTR(address, ',[^,]+',1,2)
FROM userinfo ;