Oracle: How to tell if center 3 characters are numbers?

Question

I'm trying to separate the last character which should be a letter [A-F] if the the string has 3 numbers in a row somewhere previous.

For example, 103C would return 2 separate fields 103 and C A103B would return 2 separate fields A103 and B. FX103D would return 2 separate fields FX103 and D. LOCATION2 would just return LOCATION2 and the 2nd field would be blank.

I've done something similar before with regexp_like, but I'm new to regex in general so I'm not sure how'd I'd accomplish this.

For a similar application, I've done regexp_like(c_lab.loc_code_from,'^\d{5}[[:alpha:]]') which looks at the first 5 characters, if they're numbers then the condition is satisfied and I split it up accordingly as shown below.

CASE
WHEN regexp_like(c_lab.loc_code_from,'^\d{5}[[:alpha:]]')
THEN substr(c_lab.loc_code_from, 1, 5)
ELSE c_lab.loc_code_from
END as "From Location",
CASE
WHEN regexp_like(c_lab.loc_code_from,'^\d{5}[[:alpha:]]')
THEN substr(c_lab.loc_code_from,6,1)
ELSE 'A'
END as "From Level ID"

Answer 1

YOu can use the below which is as per your requirement . The Db fiddle here

         WITH data
         AS (SELECT 'A103B' dt
             FROM   dual
             UNION
             SELECT '103C'
             FROM   dual
             UNION
             SELECT 'FX104D'
             FROM   dual
             UNION
             SELECT 'Location2'
             FROM   dual)
    SELECT Nvl(Substr(dt, 1, Instr(dt, Regexp_substr ( dt, '[[:digit:]]{3}' ))
                             + 2), dt),
           Substr(dt, Instr(dt, Regexp_substr ( dt, '[[:digit:]]{3}' ))
                      + 3)
    FROM   data; 
 

Answer 2

Try this solution which takes advantage of knowing that REGEXP_REPLACE() returns the original string if the pattern is not found (FYI - REGEXP_SUBSTR() returns NULL if the pattern is not found).

Part_1 is that match of everything up to but not including the last capital letter. Part_2 is the capital letter at the end of the string.

WITH tbl(str) AS (
  SELECT '103C' FROM dual UNION ALL
  SELECT 'A103B' FROM dual UNION ALL
  SELECT 'FX103D' FROM dual UNION ALL
  SELECT 'LOCATION2' FROM dual
)
SELECT str,
       REGEXP_REPLACE(str, '(.*\d{3})[A-Z]$', '\1') AS part_1,
       REGEXP_SUBSTR(str, '.*\d{3}([A-Z]$)', 1, 1, NULL, 1) AS part_2
from tbl;


STR        PART_1     PART_2   
---------- ---------- ---------
103C       103        C        
A103B      A103       B        
FX103D     FX103      D        
LOCATION2  LOCATION2           

4 rows selected.

EDIT to answer question in the comment. Wrap the call to REGEXP_SUBSTR() in a call to NVL():

NVL(REGEXP_SUBSTR(str, '.*\d{3}([A-Z]$)', 1, 1, NULL, 1), 'A') AS part_2

Answer 3

You can use:

SELECT value,
       REGEXP_REPLACE(value, '^(.*\d{3}.*)([A-F])$', '\1') AS part1,
       REGEXP_SUBSTR(value, '^(.*\d{3}.*)([A-F])$', 1, 1, NULL, 2) AS part2
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT 'A103B'     FROM DUAL UNION ALL
  SELECT '103C'      FROM DUAL UNION ALL
  SELECT 'FX104D'    FROM DUAL UNION ALL
  SELECT 'Location2' FROM DUAL;

Outputs:

VALUE	PART1	PART2
A103B	A103	B
103C	103	C
FX104D	FX104	D
Location2	Location2	null

fiddle