Oracle -在第n个字符出现之前获取所有字符

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,

现在我希望我的查询总是在逗号第5次出现之前返回所有内容，

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.

我一直在尝试一些substr或regexes，但是我想不明白。

2 个解决方案

#1

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

INSTR函数有您所需要的来找到第n个子字符串的位置——请参见出现参数。

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).

要获取字符串的一部分直到该位置使用子字符串。为了避免没有第n个符号的情况，使用NVL(或合并)。

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

例如(用N替换5并插入列):

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

#2

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

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

123456、123456、123456、123456、123456

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

如果逗号之间的字符数不总是相同，则此操作将有效。