Search code examples
sqlapache-sparkhiveapache-spark-sqlhiveql

How to use like and % in Hive with backslash in string?

I need to retrieve the records from a hive table that has specific string . My hive table has a column with data like this firstpath\secondpath\secondpathxyz

I want to fetch all records which has \secondpath\.For this I am using query like below

select* from table where columnname like %\\secondpath\\% but it always return empty rows for me .I tried even escaping with \\\\ (like %\\\\secondpath\\\\%).But this also did not help .

The above query does return me result when I use like %secondpath% but it does not check for backslash .

Any help on this will be appreciated .

Solution

  • Slash / is not a special character and does not require escaping. Backslash \ is a special character.

    Both rlike '/secondpath/' and like '%/secondpath/%' work fine.

    For backslash use rlike with four backslashes:

     rlike '\\\\secondpath\\\\'