Find function in HIVE

Question

I want to check if a field contains a string.

I want a function that would look like this:

FIND("string_to_find",field_to_search)

My data looks like this:

field_to_search
---------------
"no match in this string"
"record 2 has no matches"
"ahh finally xxxstring_to_findxxx is here"

I am looking for a function that identifies that the specified string is contained and at what position the string starts.

return
------
-1
-1
15

Answer 1

The built in locate function does nearly exactly what you need except that for your input, it would return

return
------
0
0
16

Since it indexes from 1. So all you need to do is:

Select locate("string_to_find","ahh finally xxxstring_to_findxxx is here") -1; --returns 15
Select locate("string_to_find","foo") -1; --returns -1