regexp_substr查找两个字母单词

Question

我在Oracle中有一个地址字段，我需要从其中提取国家代码。

select  '111 BROADWAY ~ ST LOUIS, WA 58585' from dual   
union  
select  '111 BROADWAY ~ ST LOUIS, WA' from dual  
union  
select  '111 BROADWAY ~ ST LOUIS,58585 WA' from dual

有时，状态代码WA在Postal Code之前，有时在后面。在某些情况下，Postal code也不存在。

在所有这些情况下，需要帮助提取WA。

Answer 1

下面是如何找到最后一个子字符串，该子字符串由两个字母组成，前面有一个空格，后面跟着一个空格或字符串的结尾：

select ...., 
       regexp_substr(address, '.*( ([[:alpha:]]{2})( |$))', 1, 1, null, 2) as state_code
...........

正则表达式查找一个空格，后面跟着两个字母，后面跟着一个空格或字符串的末尾。在此之前，.*保证我们找到了最后一个这样的事件。然后，REGEXP函数只返回两个字母(第二对圆括号中的模式部分，从左边计数；这是REGEXP_SUBSTR的最后一个参数的意思)。

Answer 2

在模式参数中放置美国的缩写代码(2个字母)

这只是一个数学人的答案的排列。由于我们已经定义了和相对较短的州代码列表，我们可以将状态代码(来自usps网站)放置在模式参数中，并使用交替运算符将它们分开。

SCOTT@db>WITH tst AS (
  2      SELECT
  3          '111 BROADWAY ~ ST LOUIS, WA 58585' us_addrss
  4      FROM
  5          dual
  6      UNION ALL
  7      SELECT
  8          '111 BROADWAY ~ ST LOUIS, WA' us_addrss
  9      FROM
 10          dual
 11      UNION ALL
 12      SELECT
 13          '111 BROADWAY ~ ST LOUIS,58585 WA' us_addrss
 14      FROM
 15          dual
 16  ) SELECT
 17      regexp_substr(t.us_addrss,'( |,)(AL|AK|AS|AZ|AR|CA|CO|CT|DE|DC|FM|FL|GA|GU|HI|ID|IL|IN|IA|KS|KY|LA|ME|MH|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|MP|OH|OK|OR|PW|PA|PR|RI|SC|SD|TN|TX|UT|VT|VI|VA|WA|WV|WI|WY)( |$)' state_code
 18  ,1,1,'c',2)
 19    FROM
 20      tst t;
state_code
------
WA
WA
WA

其他两个字母匹配将不包括在内。此外，我使用的match参数'c'，将调用case来匹配。

这个案例要求没有被识别出来，但是根据提供的样本，它看起来是一致的。

更多解释：

-My匹配模式使用三个字符组，我在子表达式参数中引用第二个字符组。

-I检查两个字母代码前面的空格或逗号。在您的示例中，在州代码之前总是有一个空格。

-I检查在状态代码之后结束的空格或列。