Unable to get result of regex expression in pyspark dataframe

Question

I am using Pyspark in Databricks with Spark 3.1.

I need to extract numbers from a text column in a dataframe using the regexp_extract_all function

Approach 1:

email_df11 = spark.sql("select New_id, regexp_extract_all(subject,'(?<!^DT!\\d)([D|d][T|t]\\d{12}|\\d{9,29})(?!\\d)', 1) as num_subject  from email_view")

This results num_subject column with empty lists.

However when I use a view of the same data frame and run the query below. I am able to see the output.

Approach 2:

select New_id, regexp_extract_all(subject,'same regex as above', 1) as num_subject from email_view

What do I need to change in Approach 1 in order to get a similar result.

blackbishop · Accepted Answer · 2022-01-28 00:36:13Z

2

You need to use four backslashes \\\\ to escape when using spark.sql:

email_df11 = spark.sql("select New_id, regexp_extract_all(subject,'(?<!^DT!\\\\d)([D|d][T|t]\\\\d{12}|\\\\d{9,29})(?!\\\\d)', 1) as num_subject  from email_view")

Or use python raw format string for the query:

email_df11 = spark.sql(r"select New_id, regexp_extract_all(subject,'(?<!^DT!\\d)([D|d][T|t]\\d{12}|\\d{9,29})(?!\\d)', 1) as num_subject  from email_view")

answered Jan 28, 2022 at 0:36

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Unable to get result of regex expression in pyspark dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related