CODEWITHSUNDEEP
regexoracle-databaseplsql
arcdegree
arcdegree

Reputation: 2720

Using a regular expression to match everything after a colon

I've got a string like this:

192.168.114.182:SomethingFun-1083:EOL/Nothing Here : MySQL Database 4.12
192.168.15.82:SomethingElse-1325083:All Types : PHP Version Info : 23

I'm trying to select this item in an Oracle database (using REGEXP_SUBSTR) and get all remaining text after the second colon (:).

So in the end, I'd like to get these values:

EOL/Nothing Here : MySQL Database 4.12
All Types : PHP Version Info : 23

I've tried these, but it haven't found something that works.

REGEXP_SUBSTR(title,'[^:]+',1,3) as Title -- doesn't work if last field has ":"
REGEXP_SUBSTR(title,'(?:[^:]*:)+([^:]*)') as Title

Upvotes: 3

Views: 6341

Answers (3)

Shannon Severance
Shannon Severance

Reputation: 18410

Oracle's regular expression functions tend to be CPU intensive versus alternatives. Tom Kyte's advice is "however, if you can do it without regular expressions - do it without them. regular expressions consume CPU hugely."

An alternative not using regular expressions would be substr(test_values, instr(test_values, ':', 1, 2) + 1)

SQL> create table t (test_values varchar2(100));

Table created.

SQL> insert into t values ('192.168.114.182:SomethingFun-1083:EOL/Nothing Here : MySQL Database 4.12');

1 row created.

SQL> insert into t values ('192.168.15.82:SomethingElse-1325083:All Types : PHP Version Info : 23');

1 row created.

SQL> commit;

Commit complete.

SQL> select substr(test_values, instr(test_values, ':', 1, 2) + 1)
  2  from t;

SUBSTR(TEST_VALUES,INSTR(TEST_VALUES,':',1,2)+1)
-----------------------------------------------------
EOL/Nothing Here : MySQL Database 4.12
All Types : PHP Version Info : 23

Bench-marking left as an exercise for the reader.

Upvotes: 4

bpgergo
bpgergo

Reputation: 16037

how about REGEXP_REPLACE

REGEXP_REPLACE(title,'^[^:]+:[^:]+:(.*)$', '\1') as Title

Upvotes: 7

Jared Ng
Jared Ng

Reputation: 5071

This should work:

^[^:]+:[^:]+:(.*)$

Upvotes: 1

Related Questions