sql中写标量函数生成大写拼音首字母

USE [StockManageSystemV2]
GO
/****** Object:  UserDefinedFunction [dbo].[PinYin]    Script Date: 2016-08-07 11:19:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[PinYin] ( @STR NVARCHAR(4000) )
RETURNS NVARCHAR(4000)
AS
    BEGIN
        DECLARE    @INTLEN INT;
        DECLARE    @STRRET NVARCHAR(4000);
        DECLARE    @TEMP NVARCHAR(100);
        SET @INTLEN = LEN(@STR);
        SET @STRRET = '';
        WHILE @INTLEN > 0
            BEGIN
                SET @TEMP = '';
                SELECT    @TEMP = CASE WHEN SUBSTRING(@STR, @INTLEN, 1) >= '帀' THEN 'Z'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '丫' THEN 'Y'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '夕' THEN 'X'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '屲' THEN 'W'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '他' THEN 'T'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '仨' THEN 'S'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '呥' THEN 'R'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '七' THEN 'Q'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '妑' THEN 'P'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '噢' THEN 'O'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '拏' THEN 'N'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '嘸' THEN 'M'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '垃' THEN 'L'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '咔' THEN 'K'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '丌' THEN 'J'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '铪' THEN 'H'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '旮' THEN 'G'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '发' THEN 'F'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '妸' THEN 'E'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '咑' THEN 'D'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '嚓' THEN 'C'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '八' THEN 'B'
                                     WHEN SUBSTRING(@STR, @INTLEN, 1) >= '吖' THEN 'A'
                                     ELSE RTRIM(LTRIM(SUBSTRING(@STR, @INTLEN, 1)))
                                END;
        --对于汉字特殊字符，不生成拼音码
                IF ( ASCII(@TEMP) > 127 )
                    SET @TEMP = '';
        --对于英文中小括号，不生成拼音码
                IF @TEMP = '('
                    OR @TEMP = ')'
                    SET @TEMP = '';
                SELECT    @STRRET = @TEMP + @STRRET;
                SET @INTLEN = @INTLEN - 1;
            END;
        RETURN @STRRET;
    END;

 

转载于:https://www.cnblogs.com/wenjieyatou/p/5745821.html