PostgreSQL: Filter using ilike and string with spaces

PHOTO EMBED

Mon Aug 29 2022 16:14:44 GMT+0000 (Coordinated Universal Time)

Saved by @marcopinero #sql

select id, name from table 
where name ilike all(string_to_array(replace('%'||TRIM('JOHN DOE')||'%',' ','%,%'), ','))

-- 1) it converts spaces into commas (,)
-- 2) it converts string to array using resulting comma separated string
-- 3) it apply ilike to each word in search string

-- It finds (for example) name = 'DOE, JHON'  in table
content_copyCOPY

Find string which has any of the words into a search string: 1) it converts spaces into commas (,) 2) it converts string to array using resulting comma separated string 3) it apply ilike to each word in search string It finds (for example) name = 'DOE, JHON' in table