0 votes
1 view
in BI by (13.4k points)

I need to perform below two expressions in a single THEN statement in IF clause.

REGEXP_EXTRACT_NTH([Col_name],'.*>(.*)',1)

REGEXP_REPLACE([Col_name],'(".*")\s',"")

both the operations should be checked using OR operation.

Sample:

<E05739> A service with name "1,178,302,180" is already active between Fri Apr 21 00:00:00 2017 and Thu Dec 30 00:00:00 9999. 

Expected Output:

A service with name is already active between Fri Apr 21 00:00:00 2017 and Thu Dec 30 00:00:00 9999. 

1 Answer

0 votes
by (36.7k points)
  • You may use the following regex replace operation:

^<[^>]*>\s*(.*?)"[0-9,]+"(.*)

to replace with $1$2.

Details:

  • ^ - start of string

  • <[^>]*> - a <...> substring (< + 0 or more chars other than > and then >)

  • \s* - 0+ whitespaces

  • (.*?) - Capturing group #1 (referenced to with $1 from the replacement pattern) capturing any 0+ chars other than line break chars as few as possible

  • " - a double quote

  • [0-9,]+ - 1 or more digits or ,

  • " - a "

  • (.*) - Capturing group #2 (referenced to with $2 from the replacement pattern) capturing any 0+ chars other than line break chars as many as possible (greedy quantifier * is used here).

...