Filtering for values that include backslashes ("\" or "\\")

Problem:

By default, data stored in Hive tables use the backslash ( '\') as an escape character. Consequently, if you’re trying to compare string values with backslashes in them, the backlash won’t be treated literally and no matches will be found.

Solution:

To get around this issue, you can use regex to replace the backslashes and then compare the strings. As an example, I’ve created a custom filter that checks both the base column ( [paths] ) and the parameter from a custom filter I’ve created (output parameter name = paths) and applies regex to both sides to replace any backslashes with empty strings so that the comparison is done properly:

case when '-1' = '<<paths:-1>>' then 1=1 else regexp_replace([paths],'(\\\\)',"") = regexp_replace("<<paths:-1>>",'(\\\\)',"") end

In my example I show two visuals side by side, where the one on the left is applying the custom filter expression, and the one on the right is using Dataset scope to filter the paths without a custom expression. With our custom expression, we see that our path applied in the Filter Widget ( \\example3.myproxylogs.com\syslog$ ) properly filters values in the left visual, while without the custom expression, we are not able to match on the path strings exactly due to the inclusion of the backslashes as part of the comparison in the right visual.

Before:

After:

1 Like