在SQL中模拟REGEXP行为

小编典典

在SQL中模拟REGEXP行为

sql

我已经在新的 dba.stackexchange.com上发布了这个问题（请让我知道是否必须删除它）。

我正在使用DB2数据库，据我所知，不支持regexp（没有其他库）。

因此，我无法实现类似于本文“将正则表达式匹配的功能引入SQL
”中所解释的内容。

您知道我是否可以使用SQL语句“模拟”这样的正则表达式吗？

^[aofdmep]\{1\}[a-z]\{1\}[a-z0-9]\{4\}a[sidbfkfpo]\{1\}

编辑2

https://dba.stackexchange.com/questions/651/emulate-regexp-like-behaviour-in-
sql/664#664这就是我得到的答案。

SELECT * FROM（从SYSIBM.SYSDUMMY1中选择’afr923zs’MyString）WHERE
substr（MyString，1,1）=’a’AND
substr（MyString，2,1）IN（’a’，’o’，’f’， ‘d’，’m’，’e’，’p’）AND
substr（MyString，3,1）在’a’AND’z’AND之间（substr（MyString，4,1）在’a’AND’z之间’OR
substr（MyString，4,1）在‘0’和‘9’之间）AND（substr（MyString，5,1）在’a’和’z’之间或substr（MyString，5,1）在‘0’之间AND‘9’）AND（substr（MyString，6,1）在’a’和’z’之间或substr（MyString，6,1）在‘0’AND‘9’之间）AND（substr（MyString，7,1
）在’a’和’z’之间或substr（MyString，7,1）在‘0’之间AND‘9’）AND
substr（MyString，8,1）IN（’s’，’i’，’d’，’b’，’f’，’k’，’p’，’o’）;

阅读 171

2021-03-17

共1个答案

小编典典

关于您的EDIT 2解决方案：

SELECT *
  FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) T
 WHERE substr(MyString,1,1) = 'a'
   AND substr(MyString,2,1) IN ('a','o','f','d','m','e','p')
     AND substr(MyString,3,1) BETWEEN 'a' AND 'z'
     AND (substr(MyString,4,1) BETWEEN 'a' AND 'z' OR substr(MyString,4,1) BETWEEN '0' AND '9')
     AND (substr(MyString,5,1) BETWEEN 'a' AND 'z' OR substr(MyString,5,1) BETWEEN '0' AND '9')
     AND (substr(MyString,6,1) BETWEEN 'a' AND 'z' OR substr(MyString,6,1) BETWEEN '0' AND '9')
     AND (substr(MyString,7,1) BETWEEN 'a' AND 'z' OR substr(MyString,7,1) BETWEEN '0' AND '9')
     AND substr(MyString,8,1) IN ('s','i','d','b','f','k','p','o')
;

如果字符列表很长，或者有多个范围，则可以考虑使用LIKE运算符。它可以缩短和简化代码：

SELECT *
  FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) T
 WHERE substr(MyString,1,1) = 'a'
   AND 'aofdmep' like '%'||substr(MyString,2,1)||'%'
   AND substr(MyString,3,1) BETWEEN 'a' AND 'z'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,4,1)||'%'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,5,1)||'%'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,6,1)||'%'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,7,1)||'%'
   AND 'sidbfkpo' like '%'||substr(MyString,8,1)||'%'
;

对于重复的字符列表，可以使用CROSS JOINed列常量：

SELECT *
  FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) T
  CROSS JOIN (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' alphanum FROM SYSIBM.SYSDUMMY1) T2
 WHERE substr(MyString,1,1) = 'a'
   AND 'aofdmep' like '%'||substr(MyString,2,1)||'%'
   AND substr(MyString,3,1) BETWEEN 'a' AND 'z'
   AND alphanum like '%'||substr(MyString,4,1)||'%'
   AND alphanum like '%'||substr(MyString,5,1)||'%'
   AND alphanum like '%'||substr(MyString,6,1)||'%'
   AND alphanum like '%'||substr(MyString,7,1)||'%'
   AND 'sidbfkpo' like '%'||substr(MyString,8,1)||'%'
;

在您的示例中不需要，但是CROSS JOINed“表”可以定义多个命名的字符类列。

2021-03-17