Need to extract only part after last hyphen(-) for every record in a column in Snowflake-CodePudding

I have records in a column, from which I need to extract the string (no need of numeric values) after last hyphen(-).

I tried with SPLIT_PART in Snowflake as the format is not same for all records I get NULL for couple of records. When I query need only string after last hyphen(-) symbol.

The data looks something like below.

IN-43 West india branch
BRANCH - KA - Bengaluru
REMOTE - AP - Hyderabad
FUSION - collide - AB - Writing
Rock - Allay - KA - Mysore
KA-18 Rock salt
FUSION - myth - KA - Area south

CodePudding user response：

Can you try this one?

with mydata as (
select * from values 
  (' IN-43 West india branch' ),
  ('BRANCH- KA - Bengaluru'),
  ('KA-Fusion strength (01)'),
  ('FUSION - myth - KA - Area south') tmp(column_name ))
select regexp_replace( REGEXP_SUBSTR( column_name, '.*-[0-9 ]*(.*)$',1,1,'e' ), '[^a-zA-Z ]*','')  res
from mydata;
    
 ------------------- 
|        RES        |
 ------------------- 
| West india branch |
| Bengaluru         |
| Fusion strength   |
| Area south        |
 -------------------