大写字母或小写字母转换为数字

大写字母或小写字母转换为数字a–>1,a–>1;b–>2,b–>2;c–>3,c–>3;…z–>26,z–>26
 如果非字母转换为-1

 

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