Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table_name ( id, column_name ) AS
SELECT 1, 'dablk' FROM DUAL UNION ALL
SELECT 2, 'datestblk' FROM DUAL UNION ALL
SELECT 3, 'atestblk' FROM DUAL UNION ALL
SELECT 4, 'datestbl' FROM DUAL UNION ALL
SELECT 5, 'dadatestblkblk' FROM DUAL;
5 rows affected

SELECT id,
       SUBSTR( column_name, 3, LENGTH( column_name ) - 5 )
FROM   table_name
WHERE  column_name LIKE 'da%blk';


ID
SUBSTR(COLUMN_NAME,3,LENGTH(COLUMN_NAME)-5)


1
null

2
test

5
datestblk


SELECT id,
       SUBSTR(
         column_name,
         first_a + 1,
         last_b - first_a - 1
       ) AS value
FROM   (
  SELECT t.*,
         INSTR( column_name, 'a' ) AS first_a,
         INSTR( column_name, 'b', -1 ) AS last_b
  FROM   table_name t
  WHERE  column_name LIKE '%a%b%'
)


ID
VALUE


1
null

2
test

3
test

4
test

5
datestblk