SQL - Getting the 3rd String After a Regular Expression

user_14622239 0
Viewed 120

so for example I have this data:

rmchat://room/kotak.com/Kotak_Debt_Brokers

I need to just get the "Kotak" from the string and show it in another column. Is there a way to get it? I have been searching online and I cannot seem to find a solution on this. Please help me.

STR, REGEXP_SUBSTR(STR, '(.*?)(\/||$)', 1, 2, NULL, 1)

This is my current code but it's outputting the last String which is Kotak_Debt_Brokers.

snowflake-cloud-data-platform sql database regex string
2 Answers

Use

REGEXP_SUBSTR(STR, '.*/([^_]+)', 1, 1, NULL, 1)

Or, alternatively,

REGEXP_REPLACE(STR, '.*/([^_]+).*', '\\1')

See regex proof / regex proof (regexp_replace).

Explanation

--------------------------------------------------------------------------------
  .*                       any character except \n (0 or more times
                           (matching the most amount possible))
--------------------------------------------------------------------------------
  /                        '/'
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    [^_]+                    any character except: '_' (1 or more
                             times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
  )                        end of \1
--------------------------------------------------------------------------------
  .*                       any character except \n (0 or more times
                           (matching the most amount possible))
user_113298900

I think this does what you want:

select REGEXP_SUBSTR(STR, '([^/_]+)_[^/]*$', 1, 1, 'e')

You can also try:

select REGEXP_SUBSTR(STR, '/([a-zA-Z0-9]+)_[^/]*$', 1, 1, 'e')

This returns only alphanumeric characters after the hyphen

user_11440350
Related