Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (NATIONAL_IDENTIFIER_NUMBER) AS
SELECT 'a' FROM DUAL UNION ALL
SELECT 'ab' FROM DUAL UNION ALL
SELECT 'abc' FROM DUAL UNION ALL
SELECT 'abcd' FROM DUAL UNION ALL
SELECT 'abcde' FROM DUAL UNION ALL
SELECT 'abcdef' FROM DUAL UNION ALL
SELECT 'abcdefg' FROM DUAL UNION ALL
SELECT 'abcdefgh' FROM DUAL UNION ALL
SELECT 'abcdefghi' FROM DUAL UNION ALL
SELECT 'abcdefghij' FROM DUAL UNION ALL
SELECT 'abcdefghijk' FROM DUAL UNION ALL
SELECT 'abcdefghijki' FROM DUAL
12 rows affected

SELECT SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -10, 1) AS chr1,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -9, 1) AS chr2,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -8, 1) AS chr3,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -7, 1) AS chr4,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -6, 1) AS chr5,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -5, 1) AS chr6,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -4, 1) AS chr7,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -3, 1) AS chr8,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -2, 1) AS chr9,
       SUBSTR(NATIONAL_IDENTIFIER_NUMBER,  -1, 1) AS chr10
FROM   table_name
WHERE  LENGTH(NATIONAL_IDENTIFIER_NUMBER) BETWEEN 2 AND 10


CHR1
CHR2
CHR3
CHR4
CHR5
CHR6
CHR7
CHR8
CHR9
CHR10


null
null
null
null
null
null
null
null
a
b

null
null
null
null
null
null
null
a
b
c

null
null
null
null
null
null
a
b
c
d

null
null
null
null
null
a
b
c
d
e

null
null
null
null
a
b
c
d
e
f

null
null
null
a
b
c
d
e
f
g

null
null
a
b
c
d
e
f
g
h

null
a
b
c
d
e
f
g
h
i

a
b
c
d
e
f
g
h
i
j

CHR1	CHR2	CHR3	CHR4	CHR5	CHR6	CHR7	CHR8	CHR9	CHR10
null	null	null	null	null	null	null	null	a	b
null	null	null	null	null	null	null	a	b	c
null	null	null	null	null	null	a	b	c	d
null	null	null	null	null	a	b	c	d	e
null	null	null	null	a	b	c	d	e	f
null	null	null	a	b	c	d	e	f	g
null	null	a	b	c	d	e	f	g	h
null	a	b	c	d	e	f	g	h	i
a	b	c	d	e	f	g	h	i	j