SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:      Insus.NET
-- Blog:        https://insus.cnblogs.com
-- Create date: 2019-05-23
-- Update date: 2019-05-23
-- Description: 大写字母或小写字母转换为数字A-->1,a-->1;B-->2,b-->2;C-->3,c-->3;...Z-->26,z-->26
--                如果非字母转换为-1
-- =============================================
CREATE FUNCTION [dbo].[svf_ConvertLetterToNumber] 
(
  @letter CHAR(1)
) RETURNS INT
AS
BEGIN
    DECLARE @ascii INT = -1
    IF LEN(ISNULL(@letter,'')) > 0 
    BEGIN        
        IF ASCII(@letter) % 65 + 1 <= 26
            SET @ascii = ASCII(@letter) % 65+ 1
 
        IF ASCII(@letter) % 97 + 1 <=26
            SET @ascii = ASCII(@letter) % 97 + 1
    END        
    
    RETURN @ascii
END
GO

SELECT 
[dbo].[svf_ConvertLetterToNumber]('A') AS [A],
[dbo].[svf_ConvertLetterToNumber]('a') AS [a],
[dbo].[svf_ConvertLetterToNumber]('B') AS [B],
[dbo].[svf_ConvertLetterToNumber]('b') AS [b],
[dbo].[svf_ConvertLetterToNumber]('C') AS [C],
[dbo].[svf_ConvertLetterToNumber]('c') AS [c],
[dbo].[svf_ConvertLetterToNumber]('Z') AS [Z],
[dbo].[svf_ConvertLetterToNumber]('z') AS [z],
[dbo].[svf_ConvertLetterToNumber]('@') AS [@],
[dbo].[svf_ConvertLetterToNumber]('$') AS [$]
GO