CODEWITHSUNDEEP
regexscalahiveapache-spark-sqlimpala
Rucha
Rucha

Reputation: 21

REGEX_REPLACE is not working spark, hive and scala as expected

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

Upvotes: 1

Views: 439

Answers (2)

Sathiyan S
Sathiyan S

Reputation: 1023

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

Upvotes: 0

leftjoin
leftjoin

Reputation: 38290

Tested in Hive, Impala, Spark SQL

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

Result:

1234

Upvotes: 1

Related Questions