SQL判断字符串是否为数字的方法详解_四海名汀

http://blog.sina.com.cn/u/1558925640

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

SQL判断字符串是否为数字的方法详解

(2014-11-21 15:58:15)

标签：

sql判断为数字

sqlserver判断为数字

mysql判断为数字

判断为数字的方法

判断字符串是否为数字

分类：数据库学习

在需求处理过程中，会遇到判断一批电话号码中是否含有不规则的字符，如电话号码中可能会含有：标点符号、字母、斜杠等不规则字符，那通过什么方法来检测这些号码中是否含有这些不规则字符呢？下面对几种方法做了测试。

一、SQL Server数据库下判断数字处理

1、创建测试表并插入测试数据

drop table t_test

create table t_test(user_tel varchar(20),id int identity(1,1))

insert into t_test

select '0739E000134'

union

select '0851E002699'

union

select '13854789654'

union

select 'E1528478524'

union

select '1878469751r'

union

select '18948-81456'

union

select '1,635487514'

union

select '15278965487'

union

select '15 78965487'

union

select '1521087589 '

union
select '133122５６４８７'

2、通过函数来判断数据的方法

2.1、通过 LIKE 函数来判断

select user_tel

from t_test

where user_tel like '%[^0-9]%'

-------查询结果--------

0739E000134

0851E002699

1,635487514

15 78965487

1521087589

1878469751r

18948-81456

E1528478524

注意：该方法对含有全角数字的号码无法判断。

2.2、通过PATINDEX函数来判断

PATINDEX('%[^表达式]%','字符串')表示在字符串中模糊匹配查找除表达式中每一个字符后的第一次位置。如：select PATINDEX('%[^0-9]%',s) 如返回值为0 ，这表示该s字符串全部为数字；如果返回值大于0，则表示该s字符串中含有非数字字符。

select user_tel

from t_test

where PATINDEX('%[^0-9]%',user_tel)>0

-------查询结果---------

0739E000134

0851E002699

1,635487514

15 78965487

1521087589

1878469751r

18948-81456

E1528478524

注意：该方法对含有全角数字的号码无法判断。

支持数字、小数点和正负号的用法：

select PATINDEX('%[^0-9|.|+|-]%',s)

2.3、通过ISNUMERIC()函数来判断

ISNUMERIC(字符串) 函数，当输入的表达式值为一个有效的整数、浮点数、money 或 decimal 类型时，则返回 1；否则返回 0 。

select user_tel

from t_test

where ISNUMERIC(user_tel)=0

-------查询结果--------

0851E002699
15 78965487
1878469751r
18948-81456
E1528478524
133122５６４８７

注意：通过ISNUMERIC()函数来判断时，某种情况下是无法正常判断的，如在判断表中的‘0739E000134’,‘1,635487514’和‘1521087589 ’ 这几种字符串时，其返回表示是数字型字符串，但其字符串中却含有非数字字符，这个函数的缺陷情况需要在使用中加以注意，但是该方法能对含有全角数字的号码进行判断，因此在使用建议两种方法一起结合使用。

2.4 ISNUMERIC()和PATINDEX一起使用来判断

select user_tel
from t_test
where PATINDEX('%[^0-9]%',user_tel)>0 or isnumeric(user_tel)=0

user_tel
--------------------
0739E000134
0851E002699
1,635487514
15 78965487
1521087589
1878469751r
18948-81456
E1528478524
133122５６４８７

二、MYSQL数据库判断数字处理

1、创建测试表并添加测试数据

DROP TABLE IF EXISTS t_test01;

CREATE TABLE t_test01(user_tel varchar(20),id int not null auto_increment PRIMARY KEY);

insert into t_test01(user_tel)

select '0739E000134'

union

select '0851E002699'

union

select '13854789654'

union

select 'E1528478524'

union

select '1878469751r'

union

select '18948-81456'

union

select '1,635487514'

union

select '15278965487'

union

select '15 78965487'

union

select '1521087589 '

UNION
SELECT '133122５６４８７'

2、通过函数来判断数据的方法

2.1、通过正则表达式来判断处理（两种）

SELECT user_tel

FROM t_test01

WHERE user_tel REGEXP('[^0-9]');

--------------结果--------------

0739E000134

0851E002699

E1528478524

1878469751r

18948-81456

1,635487514

15 78965487

1521087589

133122５６４８７

SELECT user_tel

FROM t_test01

WHERE user_tel REGEXP('[^[:digit:]]');

--------------结果--------------

0739E000134

0851E002699

E1528478524

1878469751r

18948-81456

1,635487514

15 78965487

1521087589

133122５６４８７

2.2、通过字符长度判断来处理

select user_tel

from t_test01

WHERE LENGTH(0+user_tel)<>LENGTH(user_tel);

--------------结果--------------

0739E000134

0851E002699

E1528478524

1878469751r

18948-81456

1,635487514

15 78965487

1521087589

133122５６４８７

2.3、通过自定义函数来判断处理

创建自定义函数源码如下：

CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024))

RETURNS tinyint(1)

DETERMINISTIC

RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+|[0-9]+\\.[0-9]*e\\+[0-9]+|[0-9]*\\.[0-9]+e\\+[0-9]+|[0-9]+e\\+[0-9]+)$'

调用该函数处理：

SELECT user_tel

FROM t_test01

WHERE ISNUMERIC(user_tel)=0;

--------------结果--------------

0739E000134

0851E002699

E1528478524

1878469751r

18948-81456

1,635487514

15 78965487

1521087589

133122５６４８７

附录：更多MYSQL正则表达式知识点可以参考 http://www.cnblogs.com/way_testlife/archive/2010/09/17/1829567.html

三、ORACLE下判断数字处理

1、创建测试表

drop table t_test02;

create table t_test02(user_tel varchar2(26),id number);

--添加测试数据

insert into t_test02

select '0739E000134',1 from dual

union

select '0851E002699',2 from dual

union

select '13854789654',3 from dual

union

select 'E1528478524',4 from dual

union

select '1878469751r',5 from dual

union

select '18948-81456',6 from dual

union

select '1,635487514',7 from dual

union

select '15278965487',8 from dual

union

select '15 78965487',9 from dual

union

select '1521087589 ',10 from dual

union

select '133122５６４８７',11 from dual

union

select '1891087566 ',12 from dual

;

commit;

select * from t_test02;

2、处理方法

2.1、采用translate函数进行判断处理

--简单示例测试

select translate('acdd','acd','ef') from dual;--ef(a由e代替，c由f代替,d就被移除)

select translate('acdd','cda','ef') from dual;--eff(c由e代替，d由f代替，a就被移除)

--如果to_string为空，或者两者都为空，那么返回char也为空。所以to_string不能为空。

select translate('acdd','cd','') from dual;

select translate('acdd','','') from dual;

select user_tel

from t_test02

where translate(replace(user_tel,' ','#'),'0123456789','#') is not null;

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

select user_tel

from t_test02

where translate(user_tel,'0123456789','#') is not null;

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

select user_tel

from t_test02

where translate(regexp_replace(user_tel,' ','#'),'0123456789','#') is not null;

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

select user_tel

from t_test02

where nvl2(translate(user_tel,'0123456789','#'),user_tel,null) is not null;

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

2.2、采用函数regexp_like函数结合正则表达式进行判断处理

select user_tel from t_test02 where regexp_like(user_tel,'[^0-9]');

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

select user_tel from t_test02 where regexp_like(user_tel,'([^[:digit:]]|[^\x00-\xff])');

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

select user_tel from t_test02 where regexp_like(user_tel,'([^[:digit:]]|[^\u0020-\u007E\uFF61-\uFF9F\uFFA0-\uFFDC\u2985-\u2986\u00A2-\u00A3\u00A5-\u00A6\u00AC\u00AF\u20A9\uFFE8-\uFFEE])');

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

2.3、采用length和regexp_replace函数判断处理

select regexp_replace(user_tel,'[^0-9]') from t_test02;

select user_tel from t_test02 where length(user_tel)<>length(regexp_replace(user_tel,'[^0-9]'));

--------查询结果-------

0739E000134

0851E002699

1,635487514

133122５６４８７

15 78965487

1521087589

1878469751r

1891087566

18948-81456

E1528478524

附录：

replace和translate的区别:

1、translate函数

语法：translate(char,from_string,to_string)

用法：返回将出现在from_string中的每个字符替换为to_string中的相应字符以后的字符串。若from_string比to_string字符串长，那么在from_string中比to_string中多出的字符将会被删除。三个参数中有一个是空，返回值也将是空值。

2、replace函数

语法：replace(char, search_string,replacement_string)

用法：将char中的字符串search_string全部转换为字符串replacement_string。

总结：

综上所述，replace与translate都是替代函数，只不过replace针对的是字符串，而translate针对的是单个字符。

3、ORACLE中的支持正则表达式的函数主要有下面四个：

1.REGEXP_LIKE ：与LIKE的功能相似

2.REGEXP_INSTR ：与INSTR的功能相似

3.REGEXP_SUBSTR ：与SUBSTR的功能相似

4.REGEXP_REPLACE ：与REPLACE的功能相似

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。

POSIX 正则表达式由标准的元字符（metacharacters）所构成：

'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。

'.' 匹配除换行符之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'+' 匹配前面的子表达式一次或多次。

'*' 匹配前面的子表达式零次或多次。

'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。

'( )' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。

\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。

字符簇：

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:punct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级

\转义符

(), (?:), (?=), [] 圆括号和方括号

*, +, ?, {n}, {n,}, {n,m} 限定符

^, $, anymetacharacter 位置和顺序

复制去Google翻译翻译结果

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：PARSENAME 函数用法详解

后一篇：SQL判断字符串是否为数字的方法详解

新浪BLOG意见反馈留言板　欢迎批评指正