Hive Query (Replace)

[upd r <up...@gmail.com>]

Hi Everyone,

Is there a way to ignore a particular character in hive query.
Example
Table1
Field name: empName
Value : xxxxxx!
Is there a way to remove  '!'  cause this prevents some join operations
 with another table containing the exact same values other than this column
with a '!' in its values.
Please help

Thanks,
UPD

Re: Hive Query (Replace)

[Lefty Leverenz <le...@gmail.com>]

I'm no expert, but could you use the regexp_replace or translate function?

>From the Hive wiki
<https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions>
:

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
>
> Returns the string resulting from replacing all substrings in
> INITIAL_STRING that match the java regular expression syntax defined in
> PATTERN with instances of REPLACEMENT, e.g. regexp_replace("foobar",
> "oo|ar", "") returns 'fb.' Note that some care is necessary in using
> predefined character classes: using '\s' as the second argument will match
> the letter s; '
> s' is necessary to match whitespace, etc.
>

translate(string input, string from, string to)
>
> Translates the input string by replacing the characters present in the
> from string with the corresponding characters in the to string. This is
> similar to the translate function inPostgreSQL
> <http://www.postgresql.org/docs/9.1/interactive/functions-string.html>.
> If any of the parameters to this UDF are NULL, the result is NULL as well
> (available as of Hive 0.10.0
> <https://issues.apache.org/jira/browse/HIVE-2418>)
>



-- Lefty


On Tue, Jul 1, 2014 at 7:47 PM, upd r <up...@gmail.com> wrote:

> Hi Everyone,
>
> Is there a way to ignore a particular character in hive query.
> Example
> Table1
> Field name: empName
> Value : xxxxxx!
> Is there a way to remove  '!'  cause this prevents some join operations
>  with another table containing the exact same values other than this column
> with a '!' in its values.
> Please help
>
> Thanks,
> UPD
>