Oracle使用regexp_substr拆分消息 [英] Oracle split message with regexp_substr

问题描述

我需要拆分消息:

 500 Oracle Parkway.Redwood Shores.*.=13

现在我对Substr1/2/4有了一些可行的解决方案

Now I have a bit worked solution for Substr1/2/4

  SELECT '500 Oracle Parkway.Redwood Shores.*.=13' string1,
  REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','.[^.]+') 
  "SUBSTR1" ,
  replace(REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+
  [^.]+'),'.',null) "SUBSTR2" ,
  REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+.[$.]+[^.]') 
  "SUBSTR3" ,
  REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[^=]+$') 
  "SUBSTR4" 
  FROM DUAL;

但是Substr3包含'='.我想至少要有.*".或'*'

However Substr3 contains '='. I'd like to have at least '.*.' or ' * '

能否请您提示我如何排除"正则表达式中的任何字符(例如'=')?

Could you please give me a hint how to "exclude" any characters (e.g. '=') in regexp?

非常感谢您的帮助！

谢谢

已解决，请参见SUBSTR3.1

      SELECT
     '500 Oracle Parkway.Redwood Shores.*.=13' string1,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','.[^.]+') 
      "SUBSTR1" ,
      replace(REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+
      [^.]+'),'.',null) "SUBSTR2" ,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+.[$.]+
      [^.]') "SUBSTR3" ,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[^.]+',1,3) 
      "SUBSTR3.1" ,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[^=]+$') 
      "SUBSTR4" 
      FROM DUAL;

推荐答案

在很大程度上考虑到Alex Poole，如果缺少列表元素之一，则格式'[^.]+'的正则表达式将失败.它将以静默方式返回不正确的数据.请改用此表格.注意我从第一个示例中删除了城市.尝试一下，您可能会感到惊讶:

With much respect to Alex Poole, the regex of the format '[^.]+' fails if one of the elements of the list is missing. It will silently return incorrent data. Please use this form instead. Note I removed the city from the first example. Try it and you may be surprised:

with t (str) as (
  select '500 Oracle Parkway..*.=13' from dual union 
  select 'One Microsoft Way.Redmond.Washington.=27' from dual
)
select str,
  regexp_substr(str, '(.*?)(\.|$)', 1, 1, NULL, 1) as substr1,
  regexp_substr(str, '(.*?)(\.|$)', 1, 2, NULL, 1) as substr2,
  regexp_substr(str, '(.*?)(\.|$)', 1, 3, NULL, 1) as substr3,
  ltrim(regexp_substr(str, '(.*?)(\.|$)', 1, 4, NULL, 1), '=') as substr4
from t;

有关更多信息，请参见此处:分割逗号分隔Oracle中列的值

See here for more info: Split comma separated values to columns in Oracle

这篇关于Oracle使用regexp_substr拆分消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！