# 字母转为大写字母

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

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
Source Code

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 '$'
Source Code

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
Source Code

posted @ 2019-05-31 20:44  Insus.NET  阅读(...)  评论(...编辑  收藏