CODEWITHSUNDEEP
sqloracle-database
fd2812
fd2812

Reputation: 23

How to get first string after character Oracle SQL

I'm trying to get first string after a character. Example is like

ABCDEF||GHJ||WERT

I need only

GHJ

I tried to use REGEXP but i couldnt do it.

Can anyone help me with please?

Thank you

Upvotes: 0

Views: 290

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think the most general solution is:

WITH tbl(str) AS (
      SELECT 'ABCDEF||GHJ||WERT' str FROM dual UNION ALL
      SELECT 'ABC|DEF||GHJ||WERT' str FROM dual UNION ALL
      SELECT 'ABClDEF||GHJ||WERT' str FROM dual 
    )
SELECT regexp_replace(str, '^.*\|\|(.*)\|\|.*', '\1')
FROM tbl;

Note that this works even if the individual elements contain punctuation or a single vertical bar -- which the other solutions do not. Here is a comparison.

Presumably, the double vertical bar is being used for maximum flexibility.

Upvotes: 2

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Something like I interpret with a separator in place, In this case it is || or | example is with oracle database

-- pattern -- > [^] represents non-matching character and + for says one or more character followed by ||
-- 3rd parameter --> starting position
-- 4th parameter --> nth occurrence
WITH tbl(str) AS
 (SELECT 'ABCDEF||GHJ||WERT' str FROM dual)
SELECT regexp_substr(str
                    ,'[^||]+'
                    ,1
                    ,2) output
FROM   tbl;

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143113

Somewhat simpler:

SQL> select regexp_substr('ABCDEF||GHJ||WERT', '\w+', 1, 2) result from dual;
                                                         ^
RES                                                      |
---                                               give me the 2nd "word"
GHJ

SQL>

which reads as: give me the 2nd word out of that string. Won't work properly if GHJ consists of several words (but that's not what your example suggests).

Upvotes: 2

Amir Kadyrov
Amir Kadyrov

Reputation: 1288

You should use regexp_substr function

select regexp_substr('ABCDEF||GHJ||WERT ', '\|{2}([^|]+)', 1, 1, 'i', 1) str
from dual;

STR
---
GHJ

Upvotes: 1

Related Questions