SQL 查找含有数字和字母的字符串|极客教程

SQL 查找含有数字和字母的字符串，你有一列含有数字和字母的字符串数据，并且希望返回那些既有字母又有数字的行。换句话说，如果一个字符串只含有数字或只含有字母，则不返回。

SQL 查找含有数字和字母的字符串问题描述

你有一列含有数字和字母的字符串数据，并且希望返回那些既有字母又有数字的行。换句话说，如果一个字符串只含有数字或只含有字母，则不返回。返回值应该既包含字母又包含数字，考虑如下所示的数据。

STRINGS
------------
1010 switch
333
3453430278
ClassSummary
findRow 55
threes

最终的结果集应该只有那些既含有字母又含有数字的行。

STRINGS
------------
1010 switch
findRow 55

SQL 查找含有数字和字母的字符串解决方案

使用内置函数 TRANSLATE 把每一个字母或数字转换成指定的特殊字符，然后只保留那些每种特殊字符至少都出现过一次的字符串。本解决方案使用了 Oracle 句法，但 DB2 和 PostgreSQL 也都支持 TRANSLATE，因此你能够很容易地对本解决方案做出适当改动以适用于另一个数据库。

with v as (
select 'ClassSummary' strings from dual union
select '3453430278'           from dual union
select 'findRow 55'           from dual union
select '1010 switch'          from dual union
select '333'                  from dual union
select 'threes'               from dual
)
select strings
  from (
select strings,
       translate(
         strings,
         'abcdefghijklmnopqrstuvwxyz0123456789',
         rpad('#',26,'#')||rpad('*',10,'*')) translated
  from v
       ) x
 where instr(translated,'#') > 0
   and instr(translated,'*') > 0

如果不想使用 WITH 子句，也可以使用内嵌视图或直接创建一个视图。

SQL 查找含有数字和字母的字符串扩展知识

有了 TRANSLATE 函数，本问题的解决就非常简单了。首先借助 TRANSLATE 把所有字母和数字分别替换为 # 和 *，中间结果（来自内嵌视图 X）显示如下。

with v as (
select 'ClassSummary' strings from dual union
select '3453430278'           from dual union
select 'findRow 55'           from dual union
select '1010 switch'          from dual union
select '333'                  from dual union
select 'threes'               from dual
)
select strings,
       translate(
         strings,
         'abcdefghijklmnopqrstuvwxyz0123456789',
         rpad('#',26,'#')||rpad('*',10,'*')) translated
  from v
　
STRINGS      TRANSLATED
------------ ------------
1010 switch  **** ######
333          ***
3453430278   **********
ClassSummary C####S######
findRow 55   ####R## **
threes       ######

现在，只剩下了一个问题，就是只保留那些 # 和 * 都至少出现过一次的行。使用函数 INSTR 判断一个字符串中是否包含 # 和 *。如果两种字符都出现过，那么返回值将大于 0。为了让你看得更加清楚明白，下面列出了最终返回的字符串和转换后的值。

with v as (
select 'ClassSummary' strings from dual union
select '3453430278'           from dual union
select 'findRow 55'           from dual union
select '1010 switch'          from dual union
select '333'                  from dual union
select 'threes'               from dual
)
select strings, translated
  from (
select strings,
       translate(
        strings,
        'abcdefghijklmnopqrstuvwxyz0123456789',
        rpad('#',26,'#')||rpad('*',10,'*')) translated
  from v
       )
 where instr(translated,'#') > 0
   and instr(translated,'*') > 0
　
STRINGS      TRANSLATED
------------ ------------
1010 switch  **** ######
findRow 55   ####R## **