How to remove '[' from a column

[#1]

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?

[#2]

@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.

[#3]

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 ))

[#4 · Accepted solution]

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