MSSQL 获取汉字的首拼音 MYSQL 获取汉字的首拼音

来源：互联网发布：淘宝网情侣装编辑：程序博客网时间：2024/05/04 18:13
-- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*获取汉字的首拼音*/create    function [dbo].[fun_getPY]  (     @str nvarchar(4000)  ) returns nvarchar(4000) as begin   declare @word nchar(1),@PY nvarchar(4000)   set @PY=''   while len(@str)>0   begin     set @word=left(@str,1)     --如果非汉字字符，返回原字符     set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901                then (                              select top 1 PY                              from                              (                               select 'A' as PY,N'驁' as word                              union all select 'B',N'簿'                              union all select 'C',N'錯'                      union all select 'D',N'鵽'                      union all select 'E',N'樲'                      union all select 'F',N'鰒'                      union all select 'G',N'腂'                      union all select 'H',N'夻'                      union all select 'J',N'攈'                      union all select 'K',N'穒'                      union all select 'L',N'鱳'                      union all select 'M',N'旀'                      union all select 'N',N'桛'                      union all select 'O',N'漚'                      union all select 'P',N'曝'                      union all select 'Q',N'囕'                      union all select 'R',N'鶸'                      union all select 'S',N'蜶'                      union all select 'T',N'籜'                      union all select 'W',N'鶩'                      union all select 'X',N'鑂'                      union all select 'Y',N'韻'                      union all select 'Z',N'咗'                       ) T  where word>=@word collate Chinese_PRC_CS_AS_KS_WS                     order by PY ASC                           )                        else @word                   end)     set @str=right(@str,len(@str)-1)   end   return upper(@PY)endGO-- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- 1 DELIMITER $$USE `dsp_ad_center`$$DROP FUNCTION IF EXISTS `fristPinyin`$$CREATE DEFINER=`root`@`%` FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8BEGIN    DECLARE V_RETURN VARCHAR(255);    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10), 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7, 0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),        'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');    RETURN V_RETURN;    END$$DELIMITER ;-- 2.DELIMITER $$USE `dsp_ad_center`$$DROP FUNCTION IF EXISTS `pinyin`$$CREATE DEFINER=`root`@`%` FUNCTION `pinyin`(P_NAME VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8BEGIN   DECLARE V_COMPARE VARCHAR(255);    DECLARE V_RETURN VARCHAR(255);    DECLARE I INT;    SET I = 1;    SET V_RETURN = '';    WHILE I < LENGTH(P_NAME) DOSET V_COMPARE = SUBSTR(P_NAME, I, 1);IF (V_COMPARE != '') THEN    #SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);    SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));    #SET V_RETURN = fristPinyin(V_COMPARE);END IF;SET I = I + 1;    END WHILE;    IF (ISNULL(V_RETURN) OR V_RETURN = '') THENSET V_RETURN = P_NAME;    END IF;    RETURN V_RETURN;    END$$DELIMITER ;-- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- --
0 0