SQL判断字符串里不包含字母

2022-03-16 00:15:07

Oracle:

方法一：通过To_Number 函数异常来判断,因为这个函数在转换不成功的时候是报错，所以只能用存储过程包装起来.

CREATE OR REPLACE FUNCTION Is_Number (
   str_    VARCHAR2 ) RETURN VARCHAR2
IS
   num_    NUMBER;
BEGIN
num_ := to_number(str_);
RETURN 'Y';
EXCEPTION
   WHEN OTHERS THEN
      RETURN 'N';
END Is_Number;
/

测试：

SQL> select Is_Number('12345') from dual;

IS_NUMBER('12345')
-------------------------
Y

SQL> select Is_Number('123.45') from dual;

IS_NUMBER('123.45')
--------------------------
Y

SQL> select Is_Number('123a') from dual;

IS_NUMBER('123A')
-----------------------
N

方法二：通过REGEXP_REPLACE/REGEXP_LIKE函数来实现

SQL> select nvl2(TRIM(REGEXP_replace('ss88','[0-9|+|-|.]','')),0,1) is_numerical from dual;

IS_NUMERICAL
------------
0
SQL> select nvl2(TRIM(REGEXP_REPLACE('2.88','[0-9|+|-|.]','')),0,1) is_numerical from dual;

IS_NUMERICAL
------------
1

SQL> SELECT CASE WHEN REGEXP_LIKE('1254','^[ +-.0-9]{4}$') then 1 else 0 end as is_numerical from dual;

IS_NUMERICAL
------------
1

方法三：通过 translate函数来实现,这个函数很强大,这里顺便提下它的语法和其他用处.

TRANSLATE ( 'char' , 'from_string' , 'to_string' )

TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string.
Characters in char that are not in from_string are not replaced. The argument from_string can contain more characters than to_string.
In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char,then they are removed from the return value.You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.

SELECT nvl2(translate('123','/1234567890','/'),'CHAR','NUMBER')
FROM dual ;

SQL> SELECT nvl2(translate('123','/1234567890','/'),'CHAR','NUMBER') TYPE
2 FROM dual;
TYPE
------------------------------
NUMBER

SQL>
SQL> SELECT nvl2(translate('123A','/1234567890','/'),'CHAR','NUMBER') TYPE
2 FROM dual;
TYPE
------------------------------
CHAR

在实际使用过程中，可以根据情况选用这两种方法。第一种方法的好处是可以判断带小数的数字，而第二种方法只能判断整数。

因为如果 translate 函数如果写成 translate('123A','/1234567890.','/') 这种形式是会有问题的。

SQL> select Is_Number('123.45.6') TYPE from dual;
TYPE
--------------------------------------------------------------------------------
N
SQL> SELECT nvl2(translate('1234.5.6','/1234567890.','/'),'CHAR','NUMBER') TYPE FROM dual;
TYPE
------------------------------
NUMBER

解释：

Translate中，每个from_string中的字符被to_string中相应的字符所代替。
select translate('acdd','cd','ef') from dual;→aeff

如果from_string比to_string长，那么from_string中多余的字符将被移除。
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;→(空)

实战：

如何判断一个字符串是否是数字？

解：先转换：由于to_string不能为空，我们巧用#号代替

select translate('abc123','#1234567890.','#') from dual;→abc
from_string 中的#被to_string中的#代替，但char中又没有#字符，所以通过这一步躲开了to_string必须不为空的规则。

然后后面的数字以及小数点都转换为空，于是原来的字符串中只留下abc三个字符。

转换好后，用 nvl2判断即可：

select nvl2(translate('abc123','#1234567890.','#'),'字符串','数字') from dual;→字符串

nvl2的作用就是，NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3.

从一串文本中提取数字：

select translate('用纸箱包200/箱','#'||REGEXP_replace('用纸箱包200PCS/箱','[0-9]',''),'#')from dual;

利用TRANSLATE实现关键字的过滤
有时候需要对一些关键词语进行过滤，直接使用replace的话，可能由于这些关键词语比较多而要嵌套使用，语句也不好写，同时也浪费资源。

这种情况其实可以使用TRANSLATE和replace组合使用就能完全达到目的了。比如要将“深圳”、“北京”等作为关键词语，在显示内容是要将这些词语过滤掉不显示：

--首先使用TRANSLATE将关键词语统一转换成一个特殊的字符串，比如这里的X
SQL> select TRANSLATE('上海北京天津重庆广州深圳武汉','深圳北京','XXXX') from dual;
TRANSLATE('上海北京天津重庆广?
------------------------------
上海XX天津重庆广州XX武汉
--然后用replace将特殊的字符串替换掉。注意：不能用TRANSLATE直接将关键词语直接转换为''字符串
SQL> select replace(TRANSLATE('上海北京天津重庆广州深圳武汉','深圳北京','XXXX'),'X') from dual;
REPLACE(TRANSLATE('上海北京天?
------------------------------
上海天津重庆广州武汉
SQL> --但是，用TRANSLATE是以一个字符为单位的，只要匹配到都会转换。比如不管“北”和“京”是否连接在一起都会做转换
SQL> select TRANSLATE('上海京天津重庆北广州深圳武汉','深圳北京','XXXX') from dual;
TRANSLATE('上海京天津重庆北广?
------------------------------
上海X天津重庆X广州XX武汉

TRANSLATE(string,from,to)转换的两个注意

1、转换源字串(from)在目的字串(to)中不存在对应，则转换后被截除
2、转换目的字串(to)不能为''，''在oracle中被视为空值，因此无法匹配而返回为null

另外，一个汉字作为一个字符还是两个字符进行转换与字符集的设置相关。

replace：字符串级别的代替

如：SELECT REPLACE('acdd','cd','ef') FROM dual; →aefd

translate：字符级别的代替
如：SELECT TRANSLATE('acdd','cd','ef') FROM dual; →aeff

REPLACE ( char , search_string [, replacement_string] )

REPLACE returns char with every occurrence of search_string replaced with replacement_string.
If replacement_string is omitted or null, then all occurrences of search_string are removed.
If search_string is null, then char is returned.

解释：repalce中，每个search_string都被replacement_string所代替。
select replace('acdd','cd','ef') from dual;→ aefd

如果replacement_string为空或为NULL,那么所有的search_string都被移除。
select replace('acdd','cd','') from dual;→ad

如果search_string为null,那么就返回原来的char。
select replace('acdd','','ef') from dual;→acdd
select replace('acdd','','') from dual; →acdd(也是两者都为空的情况)

Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The string returned is of VARCHAR2 datatype and is in the same character set as char.

This function provides functionality related to that provided by the TRANSLATE function.
TRANSLATE provides single-character, one-to-one substitution.
REPLACE lets you substitute one string for another as well as to remove character strings.

SQLSever：

ISNUMERIC

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following:

int	numeric
bigint	money
smallint	smallmoney
tinyint	float
decimal	real

Note
ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).

SELECT ISNUMERIC('$') as is_numerical

is_numerical

--------------

SELECT ISNUMERIC('1.0E5') as is_numerical

is_numerical

-------

ISNUMERIC 校验时认为科学计数法、货币符号均为合法，如果此类数据不加转换
直接插入数据库的float、decimal等字段中是会出异常。以下函数可用于校验字符串是否为数字类型，仅允许输入+、-、0-9、.字符.

--参数1:类型varchar 被校验字符串

--参数2:类型int 整数位数长度

--参数3:类型int 小数位数长度,若为负数既不校验小数位数长度

--返回值:类型int  返回"1"既检验正确,"0"既检验不通过(不是有效的数字类型或不符合规定的长度规范 if object_id('IS_NUMBER_NEW') is not null

drop  FUNCTION   [dbo].[IS_NUMBER_NEW]

ALTER FUNCTION [dbo].[IS_NUMBER_NEW]

              (@strVar VARCHAR(100),@i INT,@j INT)

RETURNS INT

AS

  BEGIN

    IF (ISNUMERIC(@strVar) = 0)--系统函数过滤绝大部分异常数据，但对于科学计数法等无法过滤

      RETURN 0

     ELSE

      BEGIN

        IF (@j = 0)

          BEGIN

            IF (PATINDEX('%[^0-9|+|-]%',@strVar) > 0) --整数校验，只允许+、-、数字字符

              RETURN 0

          END

         ELSE

          BEGIN

            IF (PATINDEX('%[^0-9|.|+|-]%',@strVar) > 0) --带小数校验

              RETURN 0

          END

        SET @strVar = REPLACE(REPLACE(@strVar,'-',''),'+','')--最大值校验

        IF (CHARINDEX('.',@strVar) = 0)

          BEGIN

            IF (LEN(@strVar) > @i)

              RETURN 0

          END

         ELSE

          BEGIN

            IF ((CHARINDEX('.',@strVar) - 1) > @i)

              RETURN 0

             ELSE

              IF (@j >= 0)

                BEGIN

                  IF (@j - DATALENGTH(SUBSTRING(@strVar,CHARINDEX('.',@strVar) + 1,99)) < 0)

                    RETURN 0

                END

          END

      END

    RETURN 1

  END

PATINDEX

语法
PATINDEX ( '%pattern%' , expression )

参数
pattern 一个字符串。可以使用通配符，但 pattern 之前和之后必须有 % 字符（搜索第一个和最后一个字符时除外）。pattern 是短字符数据类型类别的表达式。
expression 一个表达式，通常为要在其中搜索指定模式的列，expression 为字符串数据类型类别。
返回类型 int

注释
PATINDEX 对 text 数据类型很有用；除 IS NULL、IS NOT NULL 和 LIKE（这些是 Where 子句中对 text 类型有效的仅有的其它比较运算）外，PATINDEX 也可用于 Where 子句中。返回pattern字符串在表达式expression里第一次出现的位置，起始值从1开始算。pattern字符串在expression表达式里没找就返回0，对所有有效的文本和字符串就是有效的数据类型。

描述一下此函数的具体用法：

例一:
找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”的所有记录，那么选择语句就可能是这样：

Select Description from Northwind.dbo.Categories
Where patindex('%[b,B]read%',description) > 0
PATINDEX 中可以使用通配符来确定大写和小写的“b”

例二:
找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”,且第二子字母不是“e”的记录。

select Description from Northwind.dbo.Categories
where patindex('%[b,B]read%',description) > 0 and patindex('_[^e]%',description) = 1
通过在条件语句中增加一个使用^通配符的PATINDEX函数，我们可以过滤掉“Dessert, candies, and sweet breads”这条记录。上面的查询结果只有一条记录。

例三:

判断字符串里不包含字母

select case when PATINDEX ('%[^0-9|+|-|.]%','ss88')=0 then 1 else 0 end as is_numerical;

PATINDEX与CHARINDEX
PATINDEX函数支持使用通配符，可以用在很多有变化的查找中。而CHARINDEX不可以。根据你自己不同情况，这两个函数对你在SQL Server中的字符串的搜索、控制、分析很有帮助。

码农公寓

PATINDEX

相关文章