How to Select a substring in Oracle SQL from and up to some specific characters?

Question

i am using oracle sql. i would like to substr starting from characters XY0 and include 2 or 3 more characters until '-' sign in the string

These characters may be anywhere in the string.

Original

column_value
1st Row - Error due to XY0066- Does not fit -Not suitable
2nd Row -Error due to specific XY0089- Will not match
3rd Row -Not in good cond XY0215- Special type error

Extraction should be

result
XY0066
XY0089
XY0215

How can I do this?

score 0 · Answer 1 · answered Oct 04 '22 at 12:43

You can use:

SELECT id,
       SUBSTR(value, start_pos, end_pos - start_pos) AS code
FROM   (
  SELECT id,
         value,
         INSTR(value, 'XY') AS start_pos,
         INSTR(value, '-', INSTR(value, 'XY') + 2) AS end_pos
  FROM   table_name
);

or

SELECT id,
       SUBSTR(
         value,
         INSTR(value, 'XY'),
         INSTR(value, '-', INSTR(value, 'XY') + 2) - INSTR(value, 'XY')
       ) AS code
FROM   table_name;

or using regular expressions, which is shorter to type but will run much slower:

SELECT id,
       REGEXP_SUBSTR(value, 'XY[^-]*') AS code
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (id, value) AS
SELECT 1, 'Error due to XY0066- Does not fit -Not suitable' FROM DUAL UNION ALL
SELECT 2, 'Error due to specific XY0089- Will not match' FROM DUAL UNION ALL
SELECT 3, 'Not in good cond XY0215- Special type error' FROM DUAL;

All output:

ID	CODE
1	XY0066
2	XY0089
3	XY0215

fiddle

How to Select a substring in Oracle SQL from and up to some specific characters?

1 Answers1