Remove with regex everything after two points in the SQL query

Asked

Viewed 474 times

1

GOAL

I want to remove with regex in SQL query everything after two points, can anyone help me 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
...

Trying:

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

  • There’ll only be one : in the field?

  • exactly, just one " : "inside the STRING

  • Which bank? Oracle?

  • It’s actually an IBM DB2, but usually the same ORACLE or POSTGRESQL syntax works on it.

1 answer

2


If there’s only one : in the field, an alternative is:

select regexp_replace(hostname,':[^:]+$', '') from hosts;

The regex has the character :, and then a character class denied [^:] (any character that nay be it :), followed by quantifier + (one or more occurrences). Next we have the bookmark $, indicating the end of the string.

Which means he’s looking for : followed by one or more characters other than :, until it reaches the end of the string, and replaces all this with "nothing" (actually, an empty string), which is the same as removing this snippet.

See an example running in SQL Fiddle.

Just for the record, the regex you’re using does something completely different.

  • 1

    Perfect friend, excellent, thank you very much!!

Browser other questions tagged sql database regex

You are not signed in. Login or sign up in order to post.