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?
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 |
Perhaps, this is more generic for you:
TRIM(Both '_' FROM REGEXP_SUBSTR(Member_Target_Name), '_[^_]+_', 3, 1,'i') ) as ThirdElement
Lets say Member_Target_Name has this structure (values in square brackets are objects with values (including the brackets) :
x_[service]_[name]_[date_in_isostyle]_[type]
so a value sample would be:
s_myDB_MyApp_20250501_t
then the suggested code snippet pick up the third [name] element with text 'MyApp'.
