Regex Expression to avoid space and other character

Question

I am working with a Transformation logic in Databricks. Basically there is field called rip_fw which has values like "LC.JO.P051S1-1250" , "LF.030707 23:54-496" like this , as per business requirements a new field called base_fw has to be created where it should only have values like "LC.JO.P051S1" , "LF.030707". So from rip_fw it is only taking considering the dots and any other character after that should be violated. So i wrote a code

CASE
  WHEN INSTR(rip_fw, '.') > 0 AND REGEXP_COUNT(rip_fw,'\\.') = 2 THEN 
    -- Strict match of three alphanumeric segments separated by dots
    REGEXP_EXTRACT(rip_fw, '^([A-Za-z0-9]+\\.[A-Za-z0-9]+\\.[A-Za-z0-9]+)', 1)

  WHEN INSTR(rip_fw, '.') > 0 AND REGEXP_COUNT(rip_fw,'\\.') = 1 THEN 
    REGEXP_EXTRACT(rip_fw, '^([A-Za-z0-9]+\\.[A-Za-z0-9]+)', 1)

  ELSE rip_fw
END AS base_fw

But it seems not working as expected. can someone help me to identify what is wrong in the code?

It seems that in your strings either space or dash delimits "wanted part" from "unwanted". So i would just use ^[^ -]+ regex — Michał Turczyn
– Michał Turczyn, Commented Aug 2 at 6:39

sln · Accepted Answer · 2025-08-01 16:15:03Z

1

You should be able to combine the two case regex statements into one using an alternation.
Where the longer segment is first and the smaller subset is last.

WHEN INSTR(rip_fw, '.') > 0
    REGEXP_EXTRACT(rip_fw, '^([A-Za-z0-9]+\\.[A-Za-z0-9]+\\.[A-Za-z0-9]+|[A-Za-z0-9]+\\.[A-Za-z0-9]+)', 1)

Ideally it would be this

^([A-Za-z0-9]+(?:\\.[A-Za-z0-9]+){1,2})

if SQL REGEX has that capability.

edited Aug 1 at 16:15

answered Aug 1 at 16:08

sln

3,6431 gold badge7 silver badges13 bronze badges

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

Comments

Chris Maurer · Accepted Answer · 2025-08-01 15:48:20Z

-1

I would simplify this by using Regexp_Replace() instead of the case expression:


regexp_replace(rip_fw, '^([A-Za-z0-9]+(\\.[A-Za-z0-9]+){0,2}).*$', '$1',1)

The documentation for this function is a bit sketchy, but the $1 in the replacement is normally replaced by whatever is in the first capture group. Also if there are no matches the entire string is returned unchanged just like in your Else clause.

answered Aug 1 at 15:48

Chris Maurer

2,9321 gold badge13 silver badges9 bronze badges

Collectives™ on Stack Overflow

Regex Expression to avoid space and other character

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related