Unable to compare two columns using like operator in snowflake-CodePudding

I have a table with clean city names (table1) and another one with unclean city names data (table2).

Sample data: Table1 city: bhopal Table2 city: shivaji nagar, bhopal

I am trying to use like operator to compare table1.city with table2.city and get table1.city as an output column.

I tried the below query.

select * from table2, table1 where table1.city_name like '%' table2.city_name '%'

I am getting an error saying 'Numeric value '%' is not recognized'

CodePudding user response：

Snowflake uses the ANSI operator || for string concatenation, not . Also, you need to phrase that the second city is like/contains the first, not the other way around.

SELECT t1.city_name
FROM table1 t1
WHERE EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t2.city_name LIKE '%' || t1.city_name || '%'
);