Oracle 18c

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

with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'IISLUG60DF' as servername from dual union all
select 'XCCPTV401D' as servername from dual 
)
select DISTINCT SUBSTR(ServerName,1,6) as server_name
from MYTABLE;


SERVER_NAME


XCCPTV

IISLUG

APPQTV


with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'APPQTV1C34' as servername from dual union all
select 'IISLUG60DF' as servername from dual union all
select 'IISLUG80DF' as servername from dual union all
select 'XCCPTV401D' as servername from dual 
)
select SUBSTR(ServerName,1,6)                      as server_name
      ,substr(servername, 7, 4)                    as serverno
      ,to_number(substr(servername, 7, 4), 'xxxx') as server_no
from MYTABLE;


SERVER_NAME
SERVERNO
SERVER_NO


APPQTV
1234
4660

APPQTV
1C34
7220

IISLUG
60DF
24799

IISLUG
80DF
32991

XCCPTV
401D
16413


with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'APPQTV1C34' as servername from dual union all
select 'IISLUG60DF' as servername from dual union all
select 'IISLUG80DF' as servername from dual union all
select 'XCCPTV401D' as servername from dual 
)
select SUBSTR(ServerName,1,6)                           as server_name
      ,max(to_number(substr(servername, 7, 4), 'xxxx')) as server_no
from MYTABLE
group by SUBSTR(ServerName,1,6)


SERVER_NAME
SERVER_NO


XCCPTV
16413

IISLUG
32991

APPQTV
7220

SERVER_NAME	SERVERNO	SERVER_NO
APPQTV	1234	4660
APPQTV	1C34	7220
IISLUG	60DF	24799
IISLUG	80DF	32991
XCCPTV	401D	16413