银川市网站建设_网站建设公司_Logo设计_seo优化

在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) <= 122AND 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 = 0SET @Value = LTRIM(RTRIM(@Value))IF LEN(ISNULL(@Value,'')) >= 2 AND LEN(ISNULL(@Value,'')) <= 3BEGINDECLARE @firstCharacter NVARCHAR(1) = SUBSTRING(@Value,1,1)IF ISNULL(@firstCharacter,'') <> '' AND [dbo].[svf_IsDaxieLetter](@firstCharacter) <> 0BEGINDECLARE @secondCharacter NVARCHAR(1) = SUBSTRING(@Value,2,1)DECLARE @thirdCharacter NVARCHAR(1) = SUBSTRING(@Value,3,1)IF ISNULL(@secondCharacter,'') <> ''BEGINIF [dbo].[svf_IsShuzi](@secondCharacter) <> 0 BEGINIF @secondCharacter = 0  --紧跟字母的数字不能为0BEGINSET @returnResult = 0ENDELSEBEGIN                        IF ISNULL(@thirdCharacter,'') <> ''   --第3位字符BEGINIF [dbo].[svf_IsShuzi](@thirdCharacter) <> 0BEGINSET @returnResult = 1ENDELSEBEGINSET @returnResult = 0END    ENDELSEBEGINSET @returnResult = 1ENDENDENDELSEBEGINSET @returnResult = 0ENDENDELSEBEGINSET @returnResult = 0END            ENDELSEBEGINSET @returnResult = 0ENDENDELSEBEGINSET @returnResult = 0ENDRETURN @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
BEGINSET NOCOUNT ON;-- 去除空格SET @Line = LTRIM(RTRIM(@Line))-- 验证生产线名 IF [dbo].[svf_ValidateProducingLineName](@Line) = 0BEGINRAISERROR(N'生产线名称[%s]不符合规则。', 16, 1, @Line)RETURNENDIF ASCII(LEFT(@Line, 1)) <> ASCII(@PD)BEGINRAISERROR(N'生产线名称[%s]前缀与车间名称[%s]匹配。', 16, 1, @Line, @PD)RETURNEND-- 检查是否已存在IF EXISTS(SELECT 1 FROM [dbo].[Producing_Line] WHERE [PD] = @PD AND [Line] = @Line)BEGINRAISERROR(N'车间[%s]生产线[%s]已经添加。', 16, 1, @PD, @Line)RETURNENDDECLARE @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 = 0SET @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)BEGINSET @returnResult = 1ENDENDRETURN @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或3IF 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) = 3BEGINDECLARE @thirdChar CHAR(1) = SUBSTRING(@Value, 3, 1)IF @thirdChar NOT BETWEEN '0' AND '9'RETURN 0END    RETURN 1
END
GO

 

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