判断字符串是否为数字的方法介绍

发布时间:2020-09-15编辑:脚本学堂
判断字符串是否为数字,起初很简单的就写上了TRY...CATCH语句来判断,当时就指考虑了字符乘上数字一定会报错,就利用错误处理机制来判断是否为数字!

  判断字符串是否为数字,起初很简单的就写上了TRY...CATCH语句来判断,当时就指考虑了字符乘上数字一定会报错,就利用错误处理机制来判断是否为数字!
 

复制代码 代码如下:
CREATE FUNCTION A
(
    @str NVARCHAR(MAX)
)
RETURNS INT
AS
    BEGIN
        DECLARE @bit BIT;
        BEGIN TRY
             SELECT @str * 1;
             SET @bit = 1;
        END TRY
        BEGIN CATCH
             SET @bit = 0;
        END CATCH
        RETURN @bit;
    END

  这个方法不妥,很多的问题:
  1.题目给出的是函数,函数里面是不能套用TRY...CATCH的;
  2.如果是十六进制,八进制或者二进制的字符串怎么办?二进制或八进制分别由0-1和0-7组成,或许可以更好的识别的为数字,但十六进制就不行了,多了A-F这几个字母,这样一弄,绝对会识别为不是数字,但它确实是十六进制的数字表达法!
  3.函数要有返回值,这么写会报错:"函数中含有的 SELECT 语句无法向客户端返回数据."
  4.可能说用PRINT,同样使用PRINT也会报错:"在函数内对带副作用的运算符 'PRINT' 的使用无效."

  首先,就是要考虑,怎么把一个为八进制或十六进制的字符串转换为十进制的表达式(二进制暂时忽略掉),利用几种进制相互转换的攻势,写了一个进制转换的函数!但这里有一个问题,一个字符串我怎么判断它是八进制,十进制或者说十六进制,这里为了能更好的让程序自己去处理这个进制转换的事情,所以按照默认的进制表达式来处理了!例如八进制以0开头,十六进制以0x开头,其它的为十进制的,二进制暂不考虑!
 

复制代码 代码如下:
/************************************************
   根据传入的字符,转换为可以用的十进制;
   八进制:0开头;
   十六进制:0x开头; 
*************************************************/
CREATE FUNCTION ChangeNum
(
    @str NVARCHAR(MAX) --字符串
)
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        DECLARE
            @_Temp NVARCHAR(MAX),
            @i INT;
        
        SET @i = 1;
            
        --去掉左右两边的空格,并且全部大写;
        SET @str = LTRIM(RTRIM(UPPER(@str)));
        SET @_Temp = '';
        
        --十六进制
        IF SUBSTRING(@str,1,2) = '0X'
            BEGIN
                WHILE @i <= LEN(@str) - 2
                    BEGIN
                        IF SUBSTRING(@str,@i + 2,1) NOT BETWEEN '0' AND '9' AND SUBSTRING(@str,@i + 2,1) NOT BETWEEN 'A' AND 'F'
                            BEGIN
                                SET @_Temp = '非十六进制数字';
                                break;
                            END
                        ELSE
                            BEGIN
                                SELECT @_Temp = @_Temp +
                                    CASE
                                        WHEN SUBSTRING(@str,@i + 2,1) = 'A' THEN 10 * POWER(CAST(16 AS BIGINT),@i - 1)
                                        WHEN SUBSTRING(@str,@i + 2,1) = 'B' THEN 11 * POWER(CAST(16 AS BIGINT),@i - 1)
                                        WHEN SUBSTRING(@str,@i + 2,1) = 'C' THEN 12 * POWER(CAST(16 AS BIGINT),@i - 1)
                                        WHEN SUBSTRING(@str,@i + 2,1) = 'D' THEN 13 * POWER(CAST(16 AS BIGINT),@i - 1)
                                        WHEN SUBSTRING(@str,@i + 2,1) = 'E' THEN 14 * POWER(CAST(16 AS BIGINT),@i - 1)
                                        WHEN SUBSTRING(@str,@i + 2,1) = 'F' THEN 15 * POWER(CAST(16 AS BIGINT),@i - 1)
                                    ELSE
                                        SUBSTRING(@str,@i + 2,1) * POWER(CAST(16 AS BIGINT),@i - 1)
                                    END
                            END
                        SET @i = @i + 1;
                    END
            END
 
        --八进制     
        IF SUBSTRING(@str,1,1) = '0' AND SUBSTRING(@str,2,1) != 'X'
            BEGIN
                WHILE @i <= LEN(@str) - 1
                    BEGIN
                        IF SUBSTRING(@str,@i,1) NOT BETWEEN '0' AND '7'
                            BEGIN
                                SET @_Temp = '非八进制数字';
                                BREAK;
                            END
                        ELSE
                            BEGIN
                                SELECT @_Temp = @_Temp + SUBSTRING(@str,@i + 1,1) * POWER(CAST(8 AS BIGINT),@i - 1)
                            END
                        SET @i = @i + 1;
                    END
            END
        RETURN @_Temp;
    END

  在处理掉进制转换问题后,在处理一些其它的情况!
  比如有一个数字123456,它的表达式有多种,例如 123,456; $123456; 12,34,56; 123.456; ¥123456等表达方法,所以想到了用ISNUMERIC()来判断是否为数字,但这个貌似有一个问题,''能被识别为数字,至于一些其它的特殊符号,还没发现,欢迎指出类似这类的符号!
 

复制代码 代码如下:
CREATE FUNCTION IsNum
(
    @str NVARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
    BEGIN
        DECLARE @ErrMsg VARCHAR(MAX);
        
        SET @str = LTRIM(RTRIM(@str));
        
        --1.判断传入的字符串是否包含多个小数点.
        IF (SELECT CHARINDEX('.', LEFT(@str, LEN(@str) - CHARINDEX('.',REVERSE(@str))), 1)) > 0
            BEGIN
                SET @ErrMsg = '传入的字符串包含多个小数点';
            END
        ELSE
            BEGIN
                --2.判断首个字符是不是逗号(,),此时的逗号可以作为金钱的分隔符处理,不比较逗号的位置,例如123,456和123,4567;
                IF(CHARINDEX(',' ,@str, 1) = 1)
                    BEGIN
                        SET @ErrMsg = '传入的字符串首字符为逗号';
                    END
                ELSE
                    BEGIN
                        
                                --根据进制返回结果来判断是否是数字;
                                IF dbo.ChangeNum(@str) = ''
                                    BEGIN
                                        IF ISNUMERIC(@str) = 1 AND @str != ''
                                            BEGIN
                                                SET @ErrMsg = '传入的字符为数字:' + @str;
                                            END
                                        ELSE
                                            BEGIN
                                                SET @ErrMsg = '传入的字符非数字;';
                                            END
                                    END
                                ELSE
                                    BEGIN
                                        SET @ErrMsg = dbo.ChangeNum(@str);
                                    END
                    END
            END
        RETURN @ErrMsg;
    END
GO
 

以上就是一个判断字符是否为数字的测试版,功能不是很完善,供大家学习参考。