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

user_8688145 0
Viewed 4664

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.

oracle sql
2 Answers

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;
user_47249070

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.

user_35157090
Related