Alternatives for regexp_extract_all in Spark SQL 2.4.0

user_11653949 0
Viewed 36

There is any way to do regexp_extract_all in Spark SQL 2.4.0?

I have column with phone numbers, and I have to split them. But I don't know which delimiter is used. So i need find all substring that are matched to the regular expression.

For example, from column with string value:

|Column                                                |
|------------------------------------------------------|
|"+1(493) 140-26-90,+1(495) 140-26-92"                 |
|"+1(491) 140-24-71,+1(495) 284-99-38"                 |
|"1(492) 232-93-71,+1(411) 222-93-54,+1(499) 214-83-88"|
|"4959906451 4923691956"                               |
|"+1(926) 456-65-04,+1(926) 345-36-71"                 |
|"84999590956"                                         |
|"8 915 608 41 73"                                     |
|------------------------------------------------------|

I have to get column with array value:

|Column                                                        |
|--------------------------------------------------------------|
|["+1(493) 140-26-90", "+1(495) 140-26-92"]                    |
|["+1(491) 140-24-71", "+1(495) 284-99-38"]                    |
|["1(492) 232-93-71", "+1(411) 222-93-54", "+1(499) 214-83-88"]|
|["4959906451", "4923691956"]                                  |
|["+1(926) 456-65-04", "+1(926) 345-36-71"]                    |
|["84999590956"]                                               |
|["8 915 608 41 73"]                                           |
|--------------------------------------------------------------|

By using this regular expression:

((8|\+7|7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?([\d]{3}[\- ]?[\d]{2}[\- ]?[\d]{2})

I can find all number, and split them into array by regexp_extract_all function

But there is not such function in Spark SQL 2.4.0.

How can I do this in Spark SQL 2.4.0?

apache-spark-sql apache-spark
1 Answers

you can try this

val pattern = """((8|\+7|7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?([\d]{3}[\- ]?[\d]{2}[\- ]?[\d]{2})""".r
df.rdd.map(x => pattern.findAllIn(x.getString(0)).toList)
      .toDF("num_list")
      .show(false)

output below:

+---------------------------------------------------+
|num_list                                           |
+---------------------------------------------------+
|[(493) 140-26-90, (495) 140-26-92]                 |
|[(491) 140-24-71, (495) 284-99-38]                 |
|[(492) 232-93-71, (411) 222-93-54, (499) 214-83-88]|
|[4959906451, 4923691956]                           |
|[(926) 456-65-04, (926) 345-36-71]                 |
|[84999590956]                                      |
|[8 915 608 41 73]                                  |
+---------------------------------------------------+

the idea is to use scala findAllIn to get all the matches.

user_66599260
Related