Remove leading and trailing chacters for hive column

Question

I have a hive column which has unknow numbers of leading and trailing double quotes in column.The column has double quotes inside data as well.

For example column looks like this

I want output like below

I have written a pyspark code where I can just strip the " and it works,but I want a solution in hql. I have also tried regexp_replace like

regexp_replace(test,'^"|^""|""$|"$', "")

but this is kind of hardcoding. Can someone please provide a generic solution for this?

score 1 · Accepted Answer · answered Aug 04 '20 at 07:37

1

Try this-

val df = spark.sql("select '\"\"\"56\"7\"' as test")
    df.show(false)
    /**
      * +--------+
      * |test    |
      * +--------+
      * |"""56"7"|
      * +--------+
      */
    df.createOrReplaceTempView("table")
    spark.sql("select test, regexp_replace(test, '^\"+|\"+$', '') as test_new from table")
      .show(false)

    /**
      * +--------+--------+
      * |test    |test_new|
      * +--------+--------+
      * |"""56"7"|56"7    |
      * +--------+--------+
      */

answered Aug 04 '20 at 07:37

Som

6,193
1
11
22

@Nils, can you please check this solution? – Som Aug 05 '20 at 15:17
1

Thanks Someshwar Kale for you answer.I didnt notice because I got the answer. – Hannah Aug 06 '20 at 14:17

Remove leading and trailing chacters for hive column

1 Answers1