Postgres query combination of 2 specific string

Question

I have a table movies and nationality field, but it written diferently even if is the same information

example:
Británica Estadounidense
estadounidense Británica 
Británica y estadounidense

My query is that I have to get all the movies that are británica and estadounidense

My code:

SELECT query_name, nationality 
FROM directors 
WHERE nationality LIKE  '%ritánica%' || '%' || '%st%'

I don't know how to get when the 2 words are switched estadounidense Británica

Tim Biegeleisen · Accepted Answer · 2021-03-11 11:17:01Z

1

I would use regex here to match entire words, regardless of case:

SELECT query_name, nationality
FROM directors 
WHERE nationality ~* '\yBritánica\y' AND nationality ~* '\yEstadounidense\y';

answered Mar 11, 2021 at 11:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

is not matching the estadounidense británica

@foxDev Your data is the problem and both Gordon's and my answer should be working for you.

Gordon Linoff · Accepted Answer · 2021-03-11 11:14:13Z

1

One method is two likes:

SELECT query_name, nationality 
FROM directors 
WHERE nationality LIKE '%ritánica%' AND
      nationality LIKE '%st%'

This would, of course, also work for the full names as well.

answered Mar 11, 2021 at 11:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

is not matching the estadounidense británica @GordonLinoff

@foxDev . . . It certainly should be. I see both subqueries in that string.