Need to select first part of the string and eliminate rest after a character using REGEXP

Security Compliance - P2 - United States-Region B
Sales Leadership - Sales Strategy - E1 - United States-Region A
Site Reliability Engineering - M6 - United Kingdom

for example my data in a column looks somewhat like above, i want to use regex_substr() to eliminate everything after P2, E1, and M6 etc which are in the format of 1single capital alphabet and 1 number and its only two characters.

final output looks something like this

Security Compliance
Sales Leadership - Sales Strategy 
Site Reliability Engineering

CodePudding user response：

I would phrase this using a regex replacement:

SELECT col, REGEXP_REPLACE(col, ' - [A-Z][0-9] -.*', '') AS col_out
FROM yourTable;

This approach has an advantage over a substring approach in the event that any column value does not have the matching pattern, in which case the original value would be selected.

CodePudding user response：

You can use below function,

SELECT 
substr(column, 0, 
instr(column, REGEXP_SUBSTR(column, '(\w)(\d)')) -3)
FROM table;

Example

SELECT 
substr('Sales Leadership - Sales Strategy - E1 - United States-Region A', 0, 
instr('Sales Leadership - Sales Strategy - E1 - United States-Region A', REGEXP_SUBSTR ('Sales Leadership - Sales Strategy - E1 - United States-Region A', '(\w)(\d)')) -3);