SQL 判断含有字母和数字的字符串|极客教程

SQL 判断含有字母和数字的字符串，你想从一个表里筛选出部分行数据，筛选条件是你感兴趣的那个列只包含字母和数字字符，考虑下面的视图 V（SQL Server 用户需要把字符串连接操作符||替换为 +）。

SQL 判断含有字母和数字的字符串问题描述

你想从一个表里筛选出部分行数据，筛选条件是你感兴趣的那个列只包含字母和数字字符，考虑下面的视图 V（SQL Server 用户需要把字符串连接操作符||替换为 +）。

create view V as
select ename as data
  from emp
 where deptno=10
 union all
select ename||', $'|| cast(sal as char(4)) ||'.00' as data
  from emp
 where deptno=20
 union all
select ename|| cast(deptno as char(4)) as data
  from emp
 where deptno=30

视图 V 代表了你要查询的表，它包含如下所示的数据。

DATA
--------------------
CLARK
KING
MILLER
SMITH,  $800.00 JONES,$ 2975.00
SCOTT,  $3000.00 ADAMS,$ 1100.00
FORD, $3000.00
ALLEN30
WARD30
MARTIN30
BLAKE30
TURNER30
JAMES30

然而，你只希望从视图 V 中提取出如下所示的记录。

DATA
-------------
CLARK
KING
MILLER
ALLEN30
WARD30
MARTIN30
BLAKE30
TURNER30
JAMES30

总之，你想过滤掉那些除了字母和数字还包含其他字符的行。

SQL 判断含有字母和数字的字符串解决方案

首先找出字符串中所有可能出现的非字母数字字符，这似乎是更为直观的解决思路。但恰恰与之相反，我们发现从反面着手更容易：首先找出所有的字母字符和数字字符。如此一来，先把所有的字母字符和数字字符转换成一个单一的字符，然后就能把它们当作一个字符。这么做的好处是，经过转换处理之后这些字母和数字可以被当作一个整体来操作。一旦生成了原有字符串的副本，并把其中的字母字符和数字字符替换成某个指定的字符，很容易就可以将字母字符和数字字符从其他字符中分离出来。
DB2
使用 TRANSLATE 函数将字母字符和数字字符都替换成单一字符，然后找出那些除了该字符还包含其他字符的行。对于 DB2 用户来说，需要在视图 V 中调用 CAST 函数。否则，会因为数据类型转换错误而导致视图创建失败。转换为 CHAR 类型时尤其要注意，因为 CHAR 的长度是固定的（长度不足的部分会被填充上）。

1 select data
2   from V
3  where translate(lower(data),
4                  repeat('a',36),
5                  '0123456789abcdefghijklmnopqrstuvwxyz') =
6                  repeat('a',length(data))

MySQL
在 MySQL 中，视图 V 的语法稍有不同。

create view V as
select ename as data
  from emp
 where deptno=10
 union all
select concat(ename,', $',sal,'.00') as data
  from emp
 where deptno=20
 union all
select concat(ename,deptno) as data
  from emp
 where deptno=30

使用正则表达式能方便地找出包含非字母数字字符的行。

1 select data
2   from V
3  where data regexp '[^0-9a-zA-Z]' = 0

Oracle 和 PostgreSQL
使用 TRANSLATE 函数把字母字符和数字字符替换成单一字符，然后找出那些除了该字符还包含其他字符的行。对于 Oracle 和 PostgreSQL 而言，视图 V 不需要调用 CAST 函数。转换为 CHAR 类型时尤其要注意，因为 CHAR 的长度是固定的（长度不足的部分会被填充上）。如果确实需要转换类型，那么就转成 VARCHAR 或 VARCHAR2 类型。

1 select data
2   from V
3  where translate(lower(data),
4                  '0123456789abcdefghijklmnopqrstuvwxyz',
5                  rpad('a',36,'a')) = rpad('a',length(data),'a')

SQL Server
因为 SQL Server 不支持 TRANSLATE 函数，我们必须遍历每一行数据，并找出那些包含非字母数字字符的行。有很多种办法可以实现这一点，下面的解决方案的思路是评估每个字符的 ASCII 值。

 1  select data
 2    from (
 3  select v.data, iter.pos,
 4         substring(v.data,iter.pos,1) c,
 5         ascii(substring(v.data,iter.pos,1)) val
 6    from v,
 7         ( select id as pos from t100 ) iter
 8   where iter.pos <= len(v.data)
 9         ) x
10   group by data
11   having min(val) between 48 and 122

SQL 判断含有字母和数字的字符串扩展知识

上述解决方案的关键在于能同时查看多个字符。通过使用 TRANSLATE 函数，我们可以很容易处理全部数字或全部字符，而且不需要循环枚举并逐一查看每个字符。
DB2、Oracle 和 PostgreSQL
视图 V 的 14 行数据里只有 9 行是字母字符和数字字符。为了筛选出只包含字母字符和数字字符的行，直接使用 TRANSLATE 函数即可。在本例中，TRANSLATE 函数把字符 0 ～ 9 和 a ～ z 都转换成了 a。一旦完成了这一转换，下一步就要比较转换后的行数据和一个（与当前行的数据）具有相同长度并且只包括 a 的字符串。如果二者相同，那么我们就可以认定该字符串仅由字母和数字构成，而且不含其他字符。
使用 TRANSLATE 函数（这里以 Oracle 语法为例）。

where translate(lower(data),
                '0123456789abcdefghijklmnopqrstuvwxyz',
                rpad('a',36,'a'))

我们把全部数字和字母字符都替换成了一个独特的字符（我这里选择了 a）。一旦这种替换完成，那些仅由字母和数字组成的字符串就变成了一个由单一字符（本例中是 a）构成的字符串。这一点可以通过单独执行 TRANSLATE 函数来进行验证。

select data, translate(lower(data),
                  '0123456789abcdefghijklmnopqrstuvwxyz',
                  rpad('a',36,'a'))
  from V
　
DATA                 TRANSLATE(LOWER(DATA)
-------------------- ---------------------
CLARK                aaaaa
...
SMITH,  $800.00 aaaaa,$ aaa.aa
...
ALLEN30              aaaaaaa
...

虽然字母字符和数字字符被替换掉了，但字符串的长度并没有发生变化。由于长度是一样的，被筛选出来的行就是那些调用了 TRANSLATE 函数之后返回值里只包括 a 的行。通过比较原字符串的长度和只包含 a 的字符串长度，我们保留了相等的行，过滤掉了其他的行。

select data, translate(lower(data),
                  '0123456789abcdefghijklmnopqrstuvwxyz',
                  rpad('a',36,'a')) translated,
        rpad('a',length(data),'a') fixed
  from V
　
DATA                 TRANSLATED           FIXED
-------------------- -------------------- ----------------
CLARK                aaaaa                aaaaa
...
SMITH,  $800.00 aaaaa,$ aaa.aa       aaaaaaaaaaaaaa
...
ALLEN30              aaaaaaa              aaaaaaa
...

最后一步就是只保留那些 TRANSLATED 和 FIXED 相等的字符串。
MySQL
WHERE 子句里的表达式如下所示：

where data regexp '[^0-9a-zA-Z]' = 0

上述条件使得那些仅包含数字和字母的行会被筛选出来。方括号里的取值范围 0-9a-zA-Z 表示所有可能出现的数字和字母。符号 ^ 表示否定，因而该表达式可被解释为“非数字或非字母”。返回值等于 1 代表 TRUE，0 代表 FALSE，因此整个表达式的意思是：“执行非数字和字母字符匹配操作，并返回结果等于 FALSE 的行。”
SQL Server
首先遍历视图 V 的每一行数据，DATA 列的每一个字符都会被作为一行返回。C 列的值代表了构成 DATA 值的每一个字符。

+-----------------+------+------+------+
| data            | pos  | c    | val  |
+-----------------+------+------+------+
| ADAMS,  $1100.00 | 1 | A | 65 | | ADAMS,$ 1100.00 |    2 | D    |   68 |
| ADAMS,  $1100.00 | 3 | A | 65 | | ADAMS,$ 1100.00 |    4 | M    |   77 |
| ADAMS,  $1100.00 | 5 | S | 83 | | ADAMS,$ 1100.00 |    6 | ,    |   44 |
| ADAMS,  $1100.00 | 7 | | 32 | | ADAMS,$ 1100.00 |    8 |  $| 36 | | ADAMS,$ 1100.00 |    9 | 1    |   49 |
| ADAMS,  $1100.00 | 10 | 1 | 49 | | ADAMS,$ 1100.00 |   11 | 0    |   48 |
| ADAMS,  $1100.00 | 12 | 0 | 48 | | ADAMS,$ 1100.00 |   13 | .    |   46 |
| ADAMS,  $1100.00 | 14 | 0 | 48 | | ADAMS,$ 1100.00 |   15 | 0    |   48 |

内嵌视图 X 不仅会逐行返回 DATA 列的每一个字符，还会提供每个字符的 ASCII 值。这是 SQL Server 的专有功能，ASCII 取值范围 48 ～ 122 代表了字母字符和数字字符。理解了这一点，我们就可以对 DATA 进行分组，并过滤掉 ASCII 值不在 48 ～ 122 范围内的值。