Oracle SQL : Regexp_substr

Question

I have below sample values in a column

Abc-123-xyz
Def-456-uvw
Ghi-879-rst-123
Jkl-abc

Expected output is the third element split by '-', in case there is no third element, the last element will be retrieve.

See expected output below:

Xyz
Uvw
Rst
Abc

Thanks ahead for the help.

Welcome to Stack Overflow. You can format source code and data samples with the Code Sample {} toolbar button. I've done it for you this time. — Álvaro González
– Álvaro González, Commented Jul 30, 2013 at 8:12
its not regex_substr in your question title, oracle have regexp_substr — ajmalmhd04
– ajmalmhd04, Commented Jul 30, 2013 at 8:24
Thank you all for the edit. will improve next in my question. — Ianthe
– Ianthe, Commented Aug 1, 2013 at 3:06

ajmalmhd04 · Accepted Answer · 2013-08-02 05:42:40Z

2

SELECT initcap(nvl(regexp_substr(word, '[^-]+', 1,3),regexp_substr(word, '[^-]+', 1,2)))  FROM your_table;

edited Aug 2, 2013 at 5:42

answered Jul 30, 2013 at 8:19

ajmalmhd04

2,6126 gold badges26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick Krasnov · Accepted Answer · 2013-07-30 08:33:59Z

2

Another approach:

SQL> with t1(col) as(
  2    select 'Abc-123-xyz'     from dual union all
  3    select 'Def-456-uvw'     from dual union all
  4    select 'Ghi-879-rst-123' from dual union all
  5    select 'Jkl-Abc'         from dual
  6  )
  7  select regexp_substr( col
  8                      , '[^-]+'
  9                      , 1
 10                      , case
 11                           when regexp_count(col, '[^-]+') >= 3
 12                           then 3
 13                           else regexp_count(col, '[^-]+')
 14                        end
 15                      ) as res
 16    from t1
 17  ;

Result:

RES
---------------
xyz
uvw
rst
Abc

answered Jul 30, 2013 at 8:33

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

1 Comment

APC Over a year ago

Just to note that REGEXP_COUNT() was only introduced in 11g (the other regex functions were available in 10g)

Egor Skriptunoff · Accepted Answer · 2013-07-30 09:05:06Z

2

regexp_substr(column, '(.*?-){0,2}([^-]+)', 1, 1, '', 2)

answered Jul 30, 2013 at 9:05

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Comments

AKDADEVIL · Accepted Answer · 2013-07-30 11:13:06Z

0

You can also do it without RegEx:

with t1 as(
  select 'Abc-123-xyz' as MyText     from dual union all
  select 'Def-456-uvw'     from dual union all
  select 'Ghi-879-rst-123' from dual union all
  select 'Jkl-Abc'         from dual
)
SELECT 
  SUBSTR(t1.mytext, LENGTH(t1.mytext) - INSTR(REVERSE(t1.mytext), '-') + 2) 
FROM t1
;

answered Jul 30, 2013 at 11:13

AKDADEVIL

2061 silver badge7 bronze badges

1 Comment

AKDADEVIL Over a year ago

^ true, my fault, I thought he always wanted to get the last triple of characters...

Collectives™ on Stack Overflow

Oracle SQL : Regexp_substr

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest