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

-- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- -- MSSQL -- -- 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*获取汉字的首拼音*/
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)

end
GO
-- -- 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 utf8
BEGIN
	    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 utf8
BEGIN
	   DECLARE V_COMPARE VARCHAR(255);
	    DECLARE V_RETURN VARCHAR(255);
	    DECLARE I INT;
	    SET I = 1;
	    SET V_RETURN = '';
	    WHILE I < LENGTH(P_NAME) DO
		SET 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 = '') THEN
		SET V_RETURN = P_NAME;
	    END IF;
	    RETURN V_RETURN;
    END$$

DELIMITER ;
-- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- -- -- MYSQL -- --