REGEX_REPLACE is not working spark, hive and scala as expected

Question

I have a string AF1234 and I want to print 1234 , also sometimes I get only numbers like 25 and 23 and I want these numbers as well, hence I am trying to use a regex_replace to check the string, below is the syntax:

select substring(nvl(nullif(regexp_replace(regexp_replace('AF1234','A$',''),'[[:digit:]]',''),''),'XX'),1,2)

This works in impala but not hive

add sample input and output to get more clarity.

Sathiyan S
– Sathiyan S

2020-07-02 11:25:39 +00:00
Commented Jul 2, 2020 at 11:25 — Sathiyan S
– Sathiyan S, Commented Jul 2, 2020 at 11:25

leftjoin · Accepted Answer · 2020-07-02 11:23:23Z

1

Tested in Hive, Impala, Spark SQL

select regexp_extract('AF1234','(\\d+)',1)

Result:

answered Jul 2, 2020 at 11:23

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sathiyan S · Accepted Answer · 2020-07-02 12:54:16Z

0

If you want numbers out of alphanumeric try this,

scala> spark.sql("select regexp_replace('AF1234','[A-Z]*','')").show
+------------------------------+
|regexp_replace(AF1234, [A-Z]*, )|
+------------------------------+
|                          1234|
+------------------------------+

spark.sql("select regexp_replace('23','[A-Z]*','')").show
+----------------------------+
|regexp_replace(23, [A-Z]*, )|
+----------------------------+
|                          23|
+----------------------------+

edited Jul 2, 2020 at 12:54

answered Jul 2, 2020 at 11:29

Sathiyan S

1,0236 silver badges13 bronze badges

Collectives™ on Stack Overflow

REGEX_REPLACE is not working spark, hive and scala as expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related