https://community.cloudera.com/t5/Support-Questions/alternative-or-substitute-of-REGEXP-INSTR-function-in-Hadoop/m-p/175344#M137603 <P><A rel="user" href="https://community.cloudera.com/users/77272/suhagpandya.html" nodeid="77272">@Suhag Pandya</A></P><P>You can use<STRONG> instr function</STRONG> in hive to return the first occurance of the substring in your string.</P><PRE>select instr(string str, string substr);</PRE><P><STRONG><U>Example:-</U></STRONG></P><PRE>hive&gt; select instr('foobar','o'); //returns first position of o in foobar string +------+--+ | _c0 | +------+--+ | 2 | +------+--+</PRE><P>To return the first position of the substring from nth position in your string then use <STRONG>locate function in hive</STRONG></P><P><STRONG><U>Example:-</U></STRONG></P><PRE>hive&gt; select locate('o','fooobar',4);//returns the first occurance from 4th position +------+--+ | _c0 | +------+--+ | 4 | +------+--+</PRE><P>For <STRONG>case insensitive</STRONG> use<STRONG> lower function with locate</STRONG> function</P><P><STRONG><U>Example:-</U></STRONG></P><PRE>hive&gt; select locate('o',lower('FOOOBAR'),4); +------+--+ | _c0 | +------+--+ | 4 | +------+--+</PRE><P>There is no built in function that can gives your nth occurrence and match regex of the substr in hive yet, but you can create UDF for this functionality in hive.</P><P>-</P><P>If the Answer addressed your question, <STRONG>Click on Accept button below to accept the answer,</STRONG> That would be great help to Community users to find solution quickly for these kind of issues.</P> Wed, 02 May 2018 06:27:30 GMT Shu_ashu 2018-05-02T06:27:30Z