question How to remove '[' from a column in Archives of Support Questions (Read Only)

How to remove '[' from a column

balavignesh_nag — Sat, 15 Apr 2017 21:36:02 GMT

Hi.. Is there a way to find '[' from a column.

I have a field which has a value of '28 May 2016[3]' and I need the output as '28 May 2016' I tried with regexp and split but while using '[' im facing an error. Also please dont suggest substr because my value will change and it will contain like '7 September 2015[456]' , '2 Sep 2014[34]'. Is there any way out in hive?

Re: How to remove '[' from a column

cstanca — Sun, 16 Apr 2017 09:01:03 GMT

@Bala Vignesh N V

Actually you can still use substr, but first you need to find your "[" character with instr function. As such, you would substr from the first character to the instr position -1. For special characters you have to use an escape character.

Look here for instr and substr examples: http://hadooptutorial.info/string-functions-in-hive/#INSTRING

This is how is done in all SQL-like, e.g. Oracle, SQL Server, MySQL etc.

Re: How to remove '[' from a column

balavignesh_nag — Sun, 16 Apr 2017 18:51:36 GMT

Hi @Constantin Stanca

Thanks. At present im using the combination of substr and instr only. Just wanted to know if there are any other possibilities. My current solution is Substr('28 May 2016[35]',1,instr('28 May 2016[35]','[' - 1 ))

Re: How to remove '[' from a column

pminovic — Sun, 16 Apr 2017 23:52:29 GMT

You can use one of the following

regexp_replace(s, "\\[\\d*\\]", "");
regexp_replace(s, "\\[.*\\]", "");

The former works only on digits inside the brackets, the latter on any text. Escapes are required because both square brackets ARE special characters in regular expressions. For example:

hive> select regexp_replace("7 September 2015[456]", "\\[\\d*\\]", "");
7 September 2015