下拉列表 汉字首字母查询

一、建立获取汉字首字母的函数：

?

? CREATE OR REPLACE FUNCTION F_TRANS_PINYIN_CAPITAL(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';
ELSE
V_RETURN := V_RETURN || '0';
END IF;
END LOOP;
RETURN V_RETURN;
END;

?

经过测试可以获得这样的结果：

SELECT F_TRANS_PINYIN_CAPITAL('熊猫') FROM DUAL???
返回 xm

?

2.如果在下拉列表中如获取 如：?x:熊猫 这样的字段

??

? sql 语句的写法：

?

select NLS_INITCAP(substr(F_TRANS_PINYIN_CAPITAL(‘熊猫’), 1, 1))? ||':'||?‘熊猫’as name?from bt_ship? order by name

?

返回：? X：熊猫

?

3、把查询所得到的集合放到下拉列表中

?

4、在下拉列表中输入首字母即会跳到相应的 显示值下面

???

?

?