regexp_substr 跳过空位置

2021-12-24 00:00:00 regex oracle plsql

使用此代码返回管道分隔字符串中的第 n 个值...

With this code to return the nth value in a pipe delimited string...

regexp_substr(int_record.interfaceline, '[^|]+', 1, i)

当所有值都存在时它工作正常

it works fine when all values are present

Mike|Male|Yes|20000|Yes 所以 3rd 值为 Yes(正确)

Mike|Male|Yes|20000|Yes so the 3rd value is Yes (correct)

但是如果字符串是

Mike|Male||20000|Yes，第三个值是20000(不是我想要的)

Mike|Male||20000|Yes, the 3rd value is 20000 (not what I want)

如何告诉表达式不要跳过空值?

How can I tell the expression to not skip over the empty values?

TIA

迈克

推荐答案

好的.这应该是最适合您的解决方案.

OK. This should be the best solution for you.

SELECT
      REGEXP_REPLACE ( 'Mike|Male||20000|Yes',
                    '^([^|]*|){2}([^|]*).*$',
                    '2' )
          TEXT
FROM
      DUAL;

所以对于你的问题

SELECT
      REGEXP_REPLACE ( INCOMINGSTREAMOFSTRINGS,
                    '^([^|]*|){N-1}([^|]*).*$',
                    '2' )
          TEXT
FROM
      DUAL;

--INCOMINGSTREAMOFSTRINGS 是带分隔符的完整字符串

--INCOMINGSTREAMOFSTRINGS is your complete string with delimiter

--你应该通过n-1来获得第n个位置

--You should pass n-1 to obtain nth position

替代 2:

WITH T AS (SELECT 'Mike|Male||20000|Yes' X FROM DUAL)
SELECT
      X,
      REGEXP_REPLACE ( X,
                    '^([^|]*).*$',
                    '1' )
          Y1,
      REGEXP_REPLACE ( X,
                    '^[^|]*|([^|]*).*$',
                    '1' )
          Y2,
      REGEXP_REPLACE ( X,
                    '^([^|]*|){2}([^|]*).*$',
                    '2' )
          Y3,
      REGEXP_REPLACE ( X,
                    '^([^|]*|){3}([^|]*).*$',
                    '2' )
          Y4,
      REGEXP_REPLACE ( X,
                    '^([^|]*|){4}([^|]*).*$',
                    '2' )
          Y5
FROM
      T;

替代 3:

SELECT
      REGEXP_SUBSTR ( REGEXP_REPLACE ( 'Mike|Male||20000|Yes',
                                '|',
                                ';' ),
                   '(^|;)([^;]*)',
                   1,
                   1,
                   NULL,
                   2 )
          AS FIRST,
      REGEXP_SUBSTR ( REGEXP_REPLACE ( 'Mike|Male||20000|Yes',
                                '|',
                                ';' ),
                   '(^|;)([^;]*)',
                   1,
                   2,
                   NULL,
                   2 )
          AS SECOND,
      REGEXP_SUBSTR ( REGEXP_REPLACE ( 'Mike|Male||20000|Yes',
                                '|',
                                ';' ),
                   '(^|;)([^;]*)',
                   1,
                   3,
                   NULL,
                   2 )
          AS THIRD,
      REGEXP_SUBSTR ( REGEXP_REPLACE ( 'Mike|Male||20000|Yes',
                                '|',
                                ';' ),
                   '(^|;)([^;]*)',
                   1,
                   4,
                   NULL,
                   2 )
          AS FOURTH,
      REGEXP_SUBSTR ( REGEXP_REPLACE ( 'Mike|Male||20000|Yes',
                                '|',
                                ';' ),
                   '(^|;)([^;]*)',
                   1,
                   5,
                   NULL,
                   2 )
          AS FIFTH
FROM
      DUAL;

相关文章