SQL Server中验证大小字母和数字

在SQL Server开发中，有必要验证某个字符是否为大写字母，小写字母和数字：
Insus.NET的写法，分别写了3个函数，

验证字符是否为大写字母：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,验证字符是否为大写字母>
-- =============================================
 CREATE FUNCTION [dbo].[svf_IsDaxieLetter] 
(
  @Value NVARCHAR(4000)
) RETURNS BIT
AS
BEGIN    
    RETURN CASE WHEN LEN(ISNULL(@Value,'')) = 1 
    AND @Value NOT LIKE '[0-9]'
    AND ASCII(@Value) >= 65  
    AND ASCII(@Value) <= 90 
    AND UPPER(@Value) COLLATE Latin1_General_CS_AS = @Value THEN 1 ELSE 0 END
END
GO

 

验证字符是否为小写字母：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,验证字符是否为小写字母>
-- =============================================
CREATE FUNCTION [dbo].[svf_IsXiaoxieLetter] 
(
  @Value NVARCHAR(4000)
) RETURNS BIT
AS
BEGIN    
    RETURN CASE WHEN LEN(ISNULL(@Value,'')) = 1 
    AND @Value NOT LIKE '[0-9]'
    AND ASCII(@Value) >= 97  
    AND ASCII(@Value) <= 122
    AND LOWER(@Value) COLLATE Latin1_General_CS_AS = @Value THEN 1 ELSE 0 END
END
GO

 

验证字符是否为数字：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,验证字符是否为数字>
-- =============================================
CREATE FUNCTION [dbo].[svf_IsShuzi] 
(
  @Value NVARCHAR(4000)
) RETURNS BIT
AS
BEGIN    
    RETURN CASE WHEN LEN(ISNULL(@Value,'')) = 1 
    AND @Value LIKE '[0-9]'
    AND ASCII(@Value) >= 48  
    AND ASCII(@Value) <= 57 THEN 1 ELSE 0 END
END
GO

写这几个函数，是因为在数据插入数据时，需要验证输入的数据是否符合规则，
应用如下，验证生产线名， 大写字母开始，紧跟是1位或2位数字，但跟字母的数字不能为0

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,验证产线名是否符合规则>
-- =============================================
CREATE FUNCTION [dbo].[svf_ValidateProducingLineName]   
(
    @Value NVARCHAR(4000)
) 
RETURNS BIT
AS
BEGIN    
    DECLARE @returnResult BIT = 0

    SET @Value = LTRIM(RTRIM(@Value))

    IF LEN(ISNULL(@Value,'')) >= 2 AND LEN(ISNULL(@Value,'')) <= 3
    BEGIN
        DECLARE @firstCharacter NVARCHAR(1) = SUBSTRING(@Value,1,1)

        IF ISNULL(@firstCharacter,'') <> '' AND [dbo].[svf_IsDaxieLetter](@firstCharacter) <> 0
        BEGIN
            DECLARE @secondCharacter NVARCHAR(1) = SUBSTRING(@Value,2,1)
            DECLARE @thirdCharacter NVARCHAR(1) = SUBSTRING(@Value,3,1)
            
            IF ISNULL(@secondCharacter,'') <> ''
            BEGIN
                 IF [dbo].[svf_IsShuzi](@secondCharacter) <> 0 
                 BEGIN
                    IF @secondCharacter = 0  --紧跟字母的数字不能为0
                    BEGIN
                        SET @returnResult = 0
                    END
                    ELSE
                    BEGIN                        
                        IF ISNULL(@thirdCharacter,'') <> ''   --第3位字符
                        BEGIN
                            IF [dbo].[svf_IsShuzi](@thirdCharacter) <> 0
                            BEGIN
                                SET @returnResult = 1
                            END
                            ELSE
                            BEGIN
                                SET @returnResult = 0
                            END    
                        END
                        ELSE
                        BEGIN
                            SET @returnResult = 1
                        END
                    END
                 END
                 ELSE
                 BEGIN
                    SET @returnResult = 0
                 END
            END
            ELSE
            BEGIN
                SET @returnResult = 0
            END            
        END
        ELSE
        BEGIN
            SET @returnResult = 0
        END
    END
    ELSE
    BEGIN
        SET @returnResult = 0
    END

    RETURN @returnResult 
END

 

实现起来，是有些复杂。
最后是一个存储过程，

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,添加产线>
-- =============================================
CREATE PROCEDURE [dbo].[usp_ProducingLine_Insert]
    @PD NVARCHAR(5),
    @Line NVARCHAR(20),
    @CreatedBy NVARCHAR(30)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 去除空格
    SET @Line = LTRIM(RTRIM(@Line))
    
    -- 验证生产线名 
    IF [dbo].[svf_ValidateProducingLineName](@Line) = 0
    BEGIN
        RAISERROR(N'生产线名称[%s]不符合规则。', 16, 1, @Line)
        RETURN
    END
    
    IF ASCII(LEFT(@Line, 1)) <> ASCII(@PD)
    BEGIN
        RAISERROR(N'生产线名称[%s]前缀与车间名称[%s]匹配。', 16, 1, @Line, @PD)
        RETURN
    END

    -- 检查是否已存在
    IF EXISTS(SELECT 1 FROM [dbo].[Producing_Line] 
              WHERE [PD] = @PD AND [Line] = @Line)
    BEGIN
        RAISERROR(N'车间[%s]生产线[%s]已经添加。', 16, 1, @PD, @Line)
        RETURN
    END

    DECLARE @line_number SMALLINT = CAST(SUBSTRING(@Line,2,2) AS SMALLINT)
    
    -- 插入数据
       
END

 

供管理员或提供给前端应用。

关联与扩展，使用二进制来验证，亦是另外一个方法，

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,验证大写字母 version 1>
-- =============================================
CREATE FUNCTION [dbo].[svf_ValidateProducingLineName_v1]   
(
    @Value NVARCHAR(4000)
) 
RETURNS BIT
AS
BEGIN    
    DECLARE @returnResult BIT = 0
    
    SET @Value = LTRIM(RTRIM(@Value))

    IF LEN(ISNULL(@Value,'')) IN (2,3)
    BEGIN
        -- 使用二进制排序规则确保大小写敏感
        IF (@Value LIKE '[A-Z][1-9]' COLLATE Latin1_General_BIN AND LEN(@Value) = 2) 
            OR
           (@Value LIKE '[A-Z][1-9][0-9]' COLLATE Latin1_General_BIN AND LEN(@Value) = 3)
            BEGIN
                SET @returnResult = 1
            END
    END

    RETURN @returnResult
END
GO

 

根据实际需求，再次修改验证函数：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Insus.NET>
-- Create date: <Create Date, ,2025-12-26>
-- Description:    <Description, ,验证大写字母 version 2>
-- =============================================
CREATE FUNCTION [dbo].[svf_ValidateProducingLineName_v2]   
(
    @Value NVARCHAR(4000)
) 
RETURNS BIT
AS
BEGIN    
   -- 去除首尾空格
    SET @Value = LTRIM(RTRIM(ISNULL(@Value, '')))
    
    -- 长度必须是2或3
    IF LEN(@Value) NOT IN (2, 3)
        RETURN 0
    
    -- 第一个字符必须是大写字母
    IF ASCII(LEFT(@Value, 1)) NOT IN(SELECT [ASCII_CODE] FROM [dbo].[Producing_Department]) --结合自己实际需求，字母还要跟车间字母致
        RETURN 0
    
    -- 第二个字符必须是1-9的数字
    DECLARE @secondChar CHAR(1) = SUBSTRING(@Value, 2, 1)
    IF @secondChar NOT BETWEEN '1' AND '9'
        RETURN 0
    
    -- 如果是3个字符，第三个必须是数字
    IF LEN(@Value) = 3
    BEGIN
        DECLARE @thirdChar CHAR(1) = SUBSTRING(@Value, 3, 1)
        IF @thirdChar NOT BETWEEN '0' AND '9'
            RETURN 0
    END    
    RETURN 1
END
GO

 

SQL Server 使用正则，操作起来有些难度。验证字母，方法很多，建议使用排序，二制制和ASCII来实现。