在SQL中仿真REGEXP行为 [英] Emulate REGEXP like behaviour in SQL

问题描述

我已将此问题发布到新的 dba.stackexchange.com （请让我知道，如果我必须删除一个）。

I've posted this question on the new dba.stackexchange.com (please, let me know if I have to delete one).

我正在处理一个DB2数据库，只要我看到regexp是不支持（不附加库）。

I'm working on a DB2 database, and as far as I can see regexp is not supported (without additional libraries).

所以我无法实现类似于本文中所解释的内容使正则表达式匹配SQL的力量

So I cannot implement something similar to what is explained in this article "Bringing the Power of Regular Expression Matching to SQL"

你知道我是否可以使用SQL语句，这样的正则表达式来模拟？

Do you know if I can "emulate", with a SQL statement, a regular expression like this?

^[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 这是答案我'已经得到了。

https://dba.stackexchange.com/questions/651/emulate-regexp-like-behaviour-in-sql/664#664 this is the answer I've got.

SELECT * FROM（SELECT'afr923zs'
MyString FROM SYSIBM.SYSDUMMY1）WHERE
substr MyString，1,1）='a'AND

substr（MyString，2,1）IN
（'a'，'o'，'f'，'d'，'m '，'e'，'p'）和

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'一个'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' ）;

SELECT * FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) 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');

推荐答案

关于您的EDIT 2解决方案：

Regarding your EDIT 2 solution:

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运算符，或者如果有多个范围。它可以缩短和简化代码：

You might consider using the LIKE operator when you have a long list of characters, or if you have multiple ranges. It can shorten and simplify the code:

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列常量：

For repeated character lists you could use a CROSS JOINed column constant:

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表可以定义多个命名的字符类列。

Not needed for your example, but the CROSS JOINed "table" could define multiple named character class columns.

这篇关于在SQL中仿真REGEXP行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！