CODEWITHSUNDEEP
sqldatabasedb2
Luis Henrique
Luis Henrique

Reputation: 771

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

enter image description here

Remove with Regex

enter image description here

DESIRED RESULT

SRV
SRV
SRV
SRV2
SRV2
SRV2
...

ATTEMPT

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

Upvotes: 0

Views: 247

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

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

Upvotes: 2

Related Questions