CODEWITHSUNDEEP
sqloracle-database
wijnand gritter
wijnand gritter

Reputation: 33

Oracle - Get all characters before the nth occurrence of a character

I am trying to get a query where I get all characters from a string before the 'n'the occurence of a character.

Say I could have the following strings:

'123456,123456,123456'
'123456'
'123456,123456,123456,123456,123456,123456'
'123456,123456,123456,123456,123456,123456,123456'

Now I want my query to always return everything before the 5th occurence of the comma,

Result:

'123456,123456,123456'
'123456'
'123456,123456,123456,123456,123456'
'123456,123456,123456,123456,123456'

I've been trying with some substr or regexes, but I can't get my head around this.

Upvotes: 3

Views: 6005

Answers (2)

Mighty.Moogle
Mighty.Moogle

Reputation: 115

INSTR function has exactly what you need to find the position of n-th substring - see the occurrence parameter.

To get the part of a string till this location use SUBSTRING. To avoid the case when there is no Nth symbol, use NVL (or COALESCE).

For example (replace 5 with N and insert your columns):

SELECT NVL(
  SUBSTR(YOUR_COLUMN, 1,
    INSTR(YOUR_COLUMN,',',1,5) -1),
  YOUR_COLUMN)
FROM YOUR_TABLE;

Upvotes: 4

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

You can do that:

define string_value='123456,123456';
select CASE 
            WHEN (length('&string_value') - length(replace('&string_value',',',null))) >=5 
            THEN SUBSTR('&string_value',0,INSTR('&string_value',',',1,5)-1)
            ELSE '&string_value'
        END as output
from dual;

output:

123456,123456

define string_value='123456,123456,123456,123456,123456,123456';
select CASE 
            WHEN (length('&string_value') - length(replace('&string_value',',',null))) >=5 
            THEN SUBSTR('&string_value',0,INSTR('&string_value',',',1,5)-1)
            ELSE '&string_value'
        END as output
from dual;

output:

123456,123456,123456,123456,123456

This will work event if the number of character between the commas is not always the same.

Upvotes: 0

Related Questions