数据库函数实现传入汉字生成拼音首字母

一、Mysql

• 传入字符串生成第一个字符首字母

DELIMITER $$
CREATE FUNCTION `func_get_firstpy`(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 $$
CREATE FUNCTION `func_get_py`(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, 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  $$

• 调用

select func_get_py('成吉思汗');

二、Oracle

CREATE OR REPLACE FUNCTION F_GETPY(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS
	V_COMPARE VARCHAR2(100);
	V_RETURN VARCHAR2(4000);
 
	FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
	BEGIN
	  RETURN NLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M');
	END;
BEGIN 
	FOR I IN 1..LENGTH(P_NAME) LOOP
		V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
		IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN
			V_RETURN := V_RETURN ||'a';
		ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN
			V_RETURN := V_RETURN ||'b';
		ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN
			V_RETURN := V_RETURN ||'c';
		ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN
			V_RETURN := V_RETURN ||'d';
		ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN
			V_RETURN := V_RETURN ||'e';
		ELSIF V_COMPARE >= F_NLSSORT('发') AND V_COMPARE <= F_NLSSORT('猤') THEN
			V_RETURN := V_RETURN ||'f';
		ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN
			V_RETURN := V_RETURN ||'g';
		ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN
			V_RETURN := V_RETURN ||'h';
		ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN
			V_RETURN := V_RETURN ||'j';
		ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN
			V_RETURN := V_RETURN ||'k';
		ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN
			V_RETURN := V_RETURN ||'l';
		ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN
			V_RETURN := V_RETURN ||'m';
		ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN
			V_RETURN := V_RETURN ||'n';
		ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN
			V_RETURN := V_RETURN ||'o';
		ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN
			V_RETURN := V_RETURN ||'p';
		ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN
			V_RETURN := V_RETURN ||'q';
		ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN
			V_RETURN := V_RETURN ||'r';
		ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN
			V_RETURN := V_RETURN ||'s';
		ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN
			V_RETURN := V_RETURN ||'t';
		ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN
			V_RETURN := V_RETURN ||'w';
		ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN
			V_RETURN := V_RETURN ||'x';
		ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN
			V_RETURN := V_RETURN ||'y';
		ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN
			V_RETURN := V_RETURN ||'z';
		END IF;
	END LOOP;
	RETURN V_RETURN;
END;
/