SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[svf_IsNumeric]

(

  @string NVARCHAR(MAX)

) RETURNS BIT  --函数返BIT数据类型，是数字返回1，非数字返回0。

AS

BEGIN

    DECLARE @rtv BIT = 1

    DECLARE @str NVARCHAR(MAX) = LTRIM(RTRIM(ISNULL(@string,''))) --去除前后空格，如果为NULL转为''

    DECLARE @start INT = 1;

    DECLARE @end INT = LEN(@str) --获取字符串长度

    WHILE (@start <= @end)  --循环字符串每一个字符

    BEGIN

        DECLARE @Numeric VARCHAR(1) = ''

        SET @Numeric = SUBSTRING(@str, @start, @start + 1) -- 每循环一次，从左边获取一位字符

        IF ASCII(@Numeric) >= 48 AND ASCII(@Numeric) <= 57  --如果是数字

        BEGIN

            SET @start = @start + 1;

            CONTINUE  --继续循环

        END

        ELSE

        BEGIN

            SET @rtv = 0

            BREAK  --跳出循环

        END

    END

    RETURN @rtv

END

CREATE TABLE [dbo].[uTable] ([col1] NVARCHAR(20),[col2] NVARCHAR(20),[col3] NVARCHAR(20),[col4] NVARCHAR(20),[col5] NVARCHAR(20),[col6] NVARCHAR(20),[col7] NVARCHAR(20))

GO

INSERT INTO [dbo].[uTable] ([col1],[col2],[col3],[col4],[col5],[col6],[col7])

VALUES ('0.455','','SF46DG','','$%9KJ','-0034','-8554')

GO

SELECT [dbo].[svf_IsNumeric] ([col1]) AS [col1],

[dbo].[svf_IsNumeric] ([col2]) AS [col2],

[dbo].[svf_IsNumeric] ([col3]) AS [col3],

[dbo].[svf_IsNumeric] ([col4]) AS [col4],

[dbo].[svf_IsNumeric] ([col5]) AS [col5],

[dbo].[svf_IsNumeric] ([col6]) AS [col6],

[dbo].[svf_IsNumeric] ([col7]) AS [col7]

FROM [dbo].[uTable]

GO