CODEWITHSUNDEEP
sqlregexoracle-databaseoracle10gregexp-substr
Sharath Tanish
Sharath Tanish

Reputation: 51

Need to remove the exact group of characters

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.

Upvotes: 2

Views: 85

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143113

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>

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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

Upvotes: 0

Related Questions