Regex in SQL query – REMOVE AFTER

GOAL

I want to remove with regex in SQL query everything after the colon, can someone help me to mount this query? I made several attempts but without success …

Normal query

Remove with Regex

DESIRED RESULT

SRV
SRV
SRV
SRV2
SRV2
SRV2
...

ATTEMPT

select regexp_replace(
hostname,'([^,]+), (1(, |$))+', '13')
from hosts;

Answer

You might find it simpler to use REGEXP_EXTRACT

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061492.html

e.g.

WITH hosts(hostname) AS ( 
    VALUES ('SRV:MD1'),('SRV:MD2'),('SRV2:GW2') 
)
SELECT
    REGEXP_EXTRACT(hostname,'(.+):',1,1,'',1)
FROM
    hosts

which will return

 1
 ----
 SRV
 SRV
 SRV2