Oracle 21c

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

CREATE TABLE table_name (Name, Age, DB) AS
SELECT 'Alice', 24, '(DESCRIPTION = (ADDRESS = (PROTOCOL = XXX) (HOST = abc.com.xxx) (PORT = 5555)) (SERVER_NAME = xyzui) )' FROM DUAL UNION ALL  
SELECT 'Bobbi', 20, '(DESCRIPTION = (ADDRESS = (PROTOCOL = XXX) (HOST = xyz.com.xxx) (PORT = 5555)) (SERVER_NAME = avhyi) )' FROM DUAL UNION ALL  
SELECT 'Cindy', 22, '(DESCRIPTION = (ADDRESS = (PROTOCOL = XXX) (HOST = tyu.com.xxx) (PORT = 5555)) (SERVER_NAME = yuiop) )' FROM DUAL;
3 rows affected

SELECT name,
       age,
       REGEXP_SUBSTR(db, '\(HOST\s*=\s*(.*?)\)', 1, 1, NULL, 1) AS host,
       REGEXP_SUBSTR(db, '\(PORT\s*=\s*(.*?)\)', 1, 1, NULL, 1) AS port,
       REGEXP_SUBSTR(db, '\(SERVER_NAME\s*=\s*(.*?)\)', 1, 1, NULL, 1) AS server_name
FROM   table_name


NAME
AGE
HOST
PORT
SERVER_NAME


Alice
24
abc.com.xxx
5555
xyzui

Bobbi
20
xyz.com.xxx
5555
avhyi

Cindy
22
tyu.com.xxx
5555
yuiop


SELECT name,
       age,
       SUBSTR(db, host_pos, INSTR(db, ')', host_pos) - host_pos) AS host,
       SUBSTR(db, port_pos, INSTR(db, ')', port_pos) - port_pos) AS port,
       SUBSTR(db, server_pos, INSTR(db, ')', server_pos) - server_pos) AS server_name
FROM   (
  SELECT name,
         age,
         db,
         INSTR(db, '(HOST = ') + 8 AS host_pos,
         INSTR(db, '(PORT = ') + 8 AS port_pos,
         INSTR(db, '(SERVER_NAME = ') + 15 AS server_pos
  FROM   table_name
)


NAME
AGE
HOST
PORT
SERVER_NAME


Alice
24
abc.com.xxx
5555
xyzui

Bobbi
20
xyz.com.xxx
5555
avhyi

Cindy
22
tyu.com.xxx
5555
yuiop

NAME	AGE	HOST	PORT	SERVER_NAME
Alice	24	abc.com.xxx	5555	xyzui
Bobbi	20	xyz.com.xxx	5555	avhyi
Cindy	22	tyu.com.xxx	5555	yuiop