字母转为大写字母

 自定义scalar-valued function函数，把字母转换为大写字母。

字母转为大写字母a–>a;b–>b;c–>c;…z–>z
如果非字母转换为”

 

 

set ansi_nulls on
go
set quoted_identifier on
go

-- =============================================
-- author:      insus.net
-- blog:        https://insus.cnblogs.com
-- create date: 2019-05-31
-- update date: 2019-05-31
-- description: 字母转为大写字母a-->a;b-->b;c-->c;...z-->z
--              如果非字母转换为''
-- =============================================
create function [dbo].[svf_convertlettertouppercaseletter] 
(
  @letter char(1)
) returns char(1)
as
begin
    declare @uppercaseletter char(1) = ''          
    if len(isnull(@letter,'')) > 0 
    begin
        if ascii(@letter) % 97 + 1 <= 26
            set @uppercaseletter = char(ascii(@letter) - (97 - 65)) 

        if ascii(@letter) % 65 + 1 <= 26
            set @uppercaseletter = @letter
    end      
    return @uppercaseletter
end
go

 

例子演示：

 

select
[dbo].[svf_convertlettertouppercaseletter] ('a') as 'a',
[dbo].[svf_convertlettertouppercaseletter] ('a') as 'a',
[dbo].[svf_convertlettertouppercaseletter] ('b') as 'b',
[dbo].[svf_convertlettertouppercaseletter] ('b') as 'b',
[dbo].[svf_convertlettertouppercaseletter] ('c') as 'c',
[dbo].[svf_convertlettertouppercaseletter] ('c') as 'c',
[dbo].[svf_convertlettertouppercaseletter] ('z') as 'z',
[dbo].[svf_convertlettertouppercaseletter] ('z') as 'z',
[dbo].[svf_convertlettertouppercaseletter] ('$') as '$'

 

以上函数，只能对单独字母进行转换，如果一次性转换一串字符呢，这个功能可以有，参考下面：

 

set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author:      insus.net
-- blog:        https://insus.cnblogs.com
-- create date: 2019-05-31
-- update date: 2019-05-31
-- description: 小写字符转为大写字母
-- =============================================
create function [dbo].[svf_converttouppercase] 
(
    @letter nvarchar(max)
) 
returns nvarchar(max)
as
begin
    declare @uppercase nvarchar(max) = n'',@i int = 1    
    while @i <= len(@letter) 
        begin          
            declare @currentchar char(1) = substring(@letter, @i, 1)
            set @uppercase = @uppercase + 
            case when ascii(@currentchar) % 97 + 1 <= 26 
            then char(ascii(@currentchar) - (97 - 65)) 
            else
               @currentchar
            end 
   
            set @i = @i + 1 
        end
        
    return @uppercase
end
go

 

示例：