CODEWITHSUNDEEP
sqlpostgresql
Baruch
Baruch

Reputation: 13

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

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?

Upvotes: 1

Views: 39

Answers (3)

jose_bacoy
jose_bacoy

Reputation: 12704

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 || '%'
);

Upvotes: 0

Hambone
Hambone

Reputation: 16397

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

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   || '%';

Upvotes: 1

Related Questions