CODEWITHSUNDEEP
sqlpostgresqlwildcard
mpc83
mpc83

Reputation: 83

using wildcard and 'like' to compare contents of two columns in pgSQL

i have columns in two separate tables that i'm using in a join and subsequent update. i want to be able to see if one column has all of its content captured in a second column.

for instance, here is a sample of contents from each column:

city_table1 | city_table2
Portsmouth    Portsmouth, New Hampshire, USA

i want to be able to have a where clause in a select statement that will match the two columns based on the contents in city_table1 (but i can't just do a left or right trim based on content variance), so i'm envisioning something like

where city_table1 like ('%' + city_table2 '%')

is my logic off here? should i referse the two fields in this where clause? i've tried this in postgres and got no results when i know it should work if i have the syntax right.

thank you!

Upvotes: 4

Views: 1616

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Postgres uses || to concatenate strings. So:

where city_table2 like ('%' || city_table1 || '%')

Also, you have the comparison backwards. The shorter string is surrounded by the '%'.

And, this would be simpler with regular expressions:

where city_table2 ~ city_table1

Upvotes: 6

Related Questions