How to search and print the details-CodePudding

I have a below details in table with some server details

Name , Age , DB 
Ali  , 24  , (DESCRIPTION = (ADDRESS = (PROTOCOL = XXX) (HOST = abc.com.xxx) (PORT = 5555)) (SERVER_NAME = xyzui) )  
Bob  , 20  , (DESCRIPTION = (ADDRESS = (PROTOCOL = XXX) (HOST = xyz.com.xxx) (PORT = 5555)) (SERVER_NAME = avhyi) )  
Cin  , 22  ,(DESCRIPTION = (ADDRESS = (PROTOCOL = XXX) (HOST = tyu.com.xxx) (PORT = 5555)) (SERVER_NAME = yuiop) )

I want in below format the output

NAME, AGE , HOST , SERVER_NAME 
Ali , 24  , abc.com.xxx , xyzui
Bob , 20  , xyz.com.xxx , avhyi
Cin , 22  , tyu.com.xxx , yuiop

Normal sql query will not give above expected output. I tried to use CATSEARCH but no luck

CodePudding user response：

You can use (fast) simple string functions:

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
);

or (slower, but less to type) REGEXP_SUBSTR:

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

Which, for the sample data:

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;

Both output:

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

db<>fiddle here