regexp_substr跳过空位 [英] regexp_substr skips over empty positions

问题描述

使用此代码返回用竖线分隔的字符串中的第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;

这篇关于regexp_substr跳过空位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！