how to use like in sql without sensitive case code example

Example: sql like case sensitive

-- Postgresql case insensitive:
SELECT * FROM people WHERE name ILIKE 'JOHN'
-- John
-- JOHN
-- john