SQL query value (more char) longer then actual value in table

userDXxi1 0
Viewed 35

I'm trying to query with Postgres a value where my query string (value) has more char than the actual value in the column:

| id | firstName|
|:---| :--------|
| 1  | bee      |
| 2  | beeWaxer |

so for example if I query beeWax, because beeWax has bee inside it I would like it to return also bee and also beeWaxer.

if I use a ILIKE operator it will only return beeWaxer (obviously):

SELECT * FROM table WHERE firstName ILIKE '%beeWax%';

is there a query that will return both rows?

sql postgresql
3 Answers

It seems you want a LIKEin both directions: Show all names that include the search word and all names that are included in the search word. Something along the lines of:

SELECT * 
FROM table 
WHERE firstName   ILIKE '%' || :searchword || '%'
   OR :searchword ILIKE '%' || firstName   || '%';
Thorsten Kettner0

I agree with the existing answer, but if you want a little cleaner code you can also use the case insensitive regex:

SELECT *
FROM table
WHERE firstName ~* 'beeWax' or 'beeWax' ~* firstname
Hambone0

You can check if the firstname is like each row in column firstname

select * 
from table x
where   exists (
   select 1 from table  y
   where 'beeWax' like '%' || x.firstName || '%'
);
jose_bacoy0
Related