Oracle sql regular expression

Question

I have a string like this 'abc : efg : xyz' for a column in a table, all the records for this column will be like this. I want substring of this string after the last colon(:). Please do need ful help

example:

'abc : efg : xyz'  -> xyz  </br>

'abc : efg : efghj'-> efghj

I have query for this as below, but I want shorter then this query:

SELECT REPLACE (
          REGEXP_SUBSTR (
             'abc : efg : xyz',
             ':.*$',
             REGEXP_INSTR (
                'abc : efg : xyz',
                ':',
                1,
                  LENGTH ('abc : efg : xyz')
                - LENGTH (
                     REPLACE ('abc : efg : xyz',
                              ':',
                              NULL))),
             1),
          ': ') data_after_the_last_colon
  FROM DUAL

Andrew Wolfe · Accepted Answer · 2015-08-03 14:39:18Z

What you want is:

REGEXP_REPLACE (INPUTSTR, '^([^:]+ : )*', '')

Specifically, this seeks a string starting from the beginning (initial caret ^) with zero or more occurrences of a string with non-colons ([^:]+) followed by : . It replaces all of these leading strings terminated with the space-colon-space with an empty string.

The following query demonstrates how this works. I've used a factored subquery with your sample input and a half dozen other tests, and the query output has the input, my regexp_replace result, and your REPLACE (REGEXP_SUBSTR syntax (I replaced your ' abc : efg : xyz ' with the factored subquery). You can add another test case by duplicating the union all select line and changing the string for inputstr.

Oh, about doold -- your syntax couldn't handle an input string without a colon, it would throw an error that killed all query results. So I wrapped your syntax in a DECODE (doold to get all the rows back.

with sampl as (
  select           'abc : efg : xyz' as inputstr, 1 as doold from dual
  union all select 'z : b :' as inputstr, 1 as doold  from dual
  union all select 'z : b : ' as inputstr, 1 as doold  from dual
  union all select ' : a ' as inputstr, 1 as doold  from dual
  union all select ' a ' as inputstr, 0 as doold  from dual
  union all select '' as inputstr, 1 as doold  from dual
  union all select ' hij : klm : nop : qrs : tuv' as inputstr, 1 as doold  from dual
)
SELECT 
  inputstr,
  regexp_replace (inputstr, '^([^:]+ : )*', '') as bettr,
  decode (doold, 
      1, -- the following is your original expression, for comparison 
         -- purposes, edited only to replace 'abc : efg : xyz' with inputstr
         REPLACE (
          REGEXP_SUBSTR (
             inputstr,
             ':.*$',
             REGEXP_INSTR (
                inputstr,
                ':',
                1,
                  LENGTH (inputstr)
                - LENGTH (
                     REPLACE (inputstr,
                              ':',
                              NULL))),
             1),
          ': '),
          'Sorry the syntax won''t support input "' || inputstr || '"'
    ) data_after_the_last_colon
  FROM sampl
 order by doold desc, length (inputstr)

Vamsi Prabhala · Accepted Answer · 2015-08-02 19:50:40Z

1

As you say the pattern is fixed, reversing the string and looking for and getting a substring till the first semi-colon would be the easiest. You can also use trim to eliminate any leading/trailing spaces.

select reverse(substr(reverse('abc : efg : efghj'),
          1,instr(reverse('abc : efg : efghj'),':')-1)) from dual

answered Aug 2, 2015 at 19:50

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

5 Comments

Jon Heller Over a year ago

Unfortunately REVERSE is undocumented in Oracle.

Vamsi Prabhala Over a year ago

@JonHeller..Please go through this..docs.oracle.com/cd/E17952_01/refman-5.1-en/…

Jon Heller Over a year ago

That document is for MySQL.

Vamsi Prabhala Over a year ago

i suggest you try it for yourself

Jon Heller Over a year ago

It works for now, on my system. But it may not work on other systems, or in the future. Oracle has shown a willingness to remove undocumented features regardless of their popularity. For example, WM_CONCAT was an undocumented but popular function, and was removed in 12c. If the function is not listed in one of the manuals it's probably not safe to use.

daivrz · Accepted Answer · 2015-08-02 20:59:03Z

1

If the position parameter of the INSTR function is negative it will count back from the end of the string. So you could use something like this:

SELECT TRIM(SUBSTR('abc : efg : efghj',INSTR('abc : efg : efghj',':',-1) + 1))
FROM   dual;

answered Aug 2, 2015 at 20:59

daivrz

1264 bronze badges

Comments

Gary_W · Accepted Answer · 2015-08-03 15:23:12Z

Here you go. This works with NULL list elements too:

SQL> with tbl(row_nbr, str) as (
  2  select 1, 'abc : efg : xyz' from dual
  3  union
  4  select 2, 'abc : efg : efghj' from dual
  5  union
  6  select 3, 'abc :  : efghj' from dual
  7  )
  8  select row_nbr, regexp_substr(str, '(.*?)( : |$)', 1, regexp_count(str, ' \: ')+1, null, 1)
  9  from tbl
 10  order by row_nbr;

   ROW_NBR REGEXP_SUBSTR(STR
---------- -----------------
         1 xyz
         2 efghj
         3 efghj

SQL>

If you want to parse the entire list:

SQL> with tbl(row_nbr, str) as (
  2  select 1, 'abc : efg : xyz' from dual
  3  union
  4  select 2, 'abc : efg : efghj' from dual
  5  union
  6  select 3, 'abc :  : efghj' from dual
  7      )
  8  SELECT row_nbr, str,
  9             COLUMN_VALUE AS match_nbr,
 10            REGEXP_SUBSTR( str ,'(.*?)( : |$)', 1, COLUMN_VALUE, NULL, 1 ) AS match_value
 11     FROM   tbl,
 12            TABLE(
 13              CAST(
 14                MULTISET(
 15                  SELECT LEVEL
 16                  FROM   DUAL
 17                  CONNECT BY LEVEL <= REGEXP_COUNT( str ,' : ' )+1
 18                ) AS SYS.ODCINUMBERLIST
 19              )
 20            );

   ROW_NBR STR                MATCH_NBR MATCH_VALUE
---------- ----------------- ---------- -----------------
         1 abc : efg : xyz            1 abc
         1 abc : efg : xyz            2 efg
         1 abc : efg : xyz            3 xyz
         2 abc : efg : efghj          1 abc
         2 abc : efg : efghj          2 efg
         2 abc : efg : efghj          3 efghj
         3 abc :  : efghj             1 abc
         3 abc :  : efghj             2
         3 abc :  : efghj             3 efghj

9 rows selected.

SQL>

Collectives™ on Stack Overflow

Oracle sql regular expression

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related