字母转为小写字母

传入大写字母，输出为小字母，如果传入小写字母，直接输出小写字母。传入非字母，输出为‘’。

如：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_convertlettertolowercaseletter] 
(
  @letter char(1)
) returns char(1)
as
begin
    declare @lowercaseletter char(1) = ''          
    if len(isnull(@letter,'')) > 0 
    begin
        if ascii(@letter) % 65 + 1 <= 26
            set @lowercaseletter = char(ascii(@letter) + (97 - 65)) 
        
        if ascii(@letter) % 97 + 1 <= 26
            set @lowercaseletter = @letter   
    end
    return @lowercaseletter
end
go

 

例子：

select
[dbo].[svf_convertlettertolowercaseletter] ('a') as 'a',
[dbo].[svf_convertlettertolowercaseletter] ('a') as 'a',
[dbo].[svf_convertlettertolowercaseletter] ('b') as 'b',
[dbo].[svf_convertlettertolowercaseletter] ('b') as 'b',
[dbo].[svf_convertlettertolowercaseletter] ('c') as 'c',
[dbo].[svf_convertlettertolowercaseletter] ('c') as 'c',
[dbo].[svf_convertlettertolowercaseletter] ('#') as '#'

 

以上只是转换单独字母，如果转换为一串字符，字符中所有大写字母转为小写字母，那又该怎样操作呢？ insus.net另外写一个函数：

 

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_converttolowercase] 
(
    @letter nvarchar(max)
) 
returns nvarchar(max)
as
begin
    declare @lowercase nvarchar(max) = n'',@i int = 1    
    while @i <= len(@letter) 
        begin          
            declare @currentchar char(1) = substring(@letter, @i, 1)
            set @lowercase = @lowercase + 
            case when ascii(@currentchar) % 65 + 1 <= 26 
            then char(ascii(@currentchar) + (97 - 65))    
            else
               @currentchar
            end 
   
            set @i = @i + 1 
        end
        
    return @lowercase
end
go

 

实例演示：