2

I need to remove all the characters after a particular string (-->).

select 
      REGEXP_SUBSTR('-->Team Name - Red-->blue', '[^(-->)]+') 
from dual;

expected result from the above query is "Team Name - Red". But its returning "Team Name".

Its filtering out everything whenever it matches any of one character.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Sharath Tanish
  • 51
  • 3

3 Answers3

1

You can still use Regexp_Substr() analytic function :

Select Regexp_Substr('-->Team Name - Red-->blue', 
                     '-{2}>(.*?)-{2}>',1,1,null,1) as "Result"
  From dual;

Result
---------------
Team Name - Red

-{2}> ~ exactly twice occurence of - and single occurence of > e.g. ( --> )

(.*?) ~ matches anything delimited by the pattern above

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Thanks for the reply. Is there anyway that I can exclude exact group of characters like "-->" in my query, I'm actually using as below `REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( MNEMONIC, '-->' ), '[^(-->)]+' ) AS parent_Name` So the mnemonic column will be aggregated with the -->. – Sharath Tanish Aug 12 '19 at 06:25
  • you're welcome @SharathTanish . Waht did you get when you tried in that manner? – Barbaros Özhan Aug 12 '19 at 06:48
  • your suggestion is working fantastic for this situation @Barbaros Özhan. But, when there is a situation where there is `->` is exist in data, that trims off the required data as well. for ex: `Select Regexp_Substr('-->Team Name - Red->01 --> Team Name - blue->02 --> Team Name - orange->03', '[^>]+[^[:punct:]]') as "Result" From dual;` in this case, expected is `Team Name - Red->01` but it returns `Team Name - Red`. – Sharath Tanish Aug 12 '19 at 08:05
0

You could try using REGEXP_REPLACE here with a capture group:

SELECT
    REGEXP_REPLACE('-->Team Name - Red-->blue', '.*-->(.*?)-->.*', '\1')
FROM dual;

The output from this is Team Name - Red

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks for the response @Tim Biegeleisen, But I want to achieve this using REGEXP_SUBSTR – Sharath Tanish Aug 12 '19 at 08:13
  • 1
    @SharathTanish What is your aversion to using `REGEXP_SUBSTR`? I mean, we _could_ just use base string functions like `INSTR`, but it would require more code, and be uglier than my answer. – Tim Biegeleisen Aug 12 '19 at 09:17
0

It seems that you, actually, want to return string between two --> marks. A good, old substr + instr option would be

SQL> with test (col) as
  2    (select '-->Team Name - Red-->blue' from dual)
  3  select substr(col,
  4                instr(col, '-->', 1, 1) + 3,
  5                instr(col, '-->', 1, 2) - instr(col, '-->', 1, 1) - 3
  6               ) result
  7  from test;

RESULT
---------------
Team Name - Red

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57