How to correlate rlike and regex_extract

user_14645503 0
Viewed 80

STATEMENT:1

spark.sql("select case when length(pop)>0 then regexp_extract(pop, '^[^@]+', 0) else '' end as pop from input").show(false)

STATEMENT:2

spark.sql("select case when length(oik)>0 and pop rlike '^[0-9]*$' then pop else '' end as pop from input").show(false)

How to correlate the above two statement regexp_extract and rlike,

sample input:1234@gamil.com output: 1234

sample input:1234abc@gmail.com output: ''

How can I correlate the two statements which I have given in a case when statement in spark-sql…(To combine rlike and regexp_extract) in a case when statement and to match the specified input and output?

  • First statement is for neglecting the characters after @
  • Second statement is for "if any non-numeric characters are present then it should reject from the first statement output"
apache-spark-sql apache-spark scala
2 Answers

This should work for you.

List("1234@gamil.com","1234abc@gmail.com")
    .toDF("pop")
    .createOrReplaceTempView("input")

spark.sql(
    """
      |select
      |    case
      |       when length(pop)>0 and pop rlike '^[0-9]+[a-z-A-Z]+@.*'
      |         then ''
      |       else
      |          case
      |             when pop rlike '^[0-9]+@.*'
      |              then regexp_extract(pop, '^[^@]+', 0)
      |           end
      |    end as pop from input
      |""".stripMargin)
    .show()
/*
+----+
| pop|
+----+
|1234|
|    |
+----+*/
user_87733090

You can try this code:


val pattern = """([0-9]+)@""".r


def parseId(id: String): String = 
 id match {
    case pattern(id) => id
    case _ => “"
  }

val results = spark.sql("select case when length(pop)>0 then regexp_extract(pop, '^[^@]+', 0) else '' end as pop from input").map(parseId(_)).show()
user_46211880
Related