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

概述USE [StockManageSystemV2]GO/****** Object: UserDefinedFunction [dbo].[PinYin] Script Date: 2016-08-07 11:19:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[PinYin] ( @STR NVARCHAR(4000) )RETURNS NVARCHAR(4000)ASBEGINDECLARE @INTLEN INT;DECLARE @STRRET NVARCHAR(4000);DECLARE @TEMP NVARCHAR(100);SET @INTLEN = LEN(@STR);SET @STRRET = '';WHILE @INTLEN > 0BEGINSET @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; 

<div >

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

 

总结

以上是内存溢出为你收集整理的sql中写标量函数生成大写拼音首字母全部内容，希望文章能够帮你解决sql中写标量函数生成大写拼音首字母所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。