请各位高手帮忙 oracle汉字转拼音
比如现在有个列 里面的汉字为你好 我想查出来的是N 请问各位高手如何实现 给个详细的看看 谢了
比如现在有个列 里面的汉字为你好 我想查出来的是N 请问各位高手如何实现 给个详细的看看 谢了
解决方案 »
- ORacle数据库导入的问题
- 存储过程报错(PLS-00306 :调用'PUT_LINE'时参数个数或类型错误
- 求救使用pl/sql在oracle9i和oracle7.3 database link
- 推荐数据字典工具
- orcal 数据库导入sqlserver 数据库中的问题
- SQL中的这个存储过程在ORACLE中怎么写
- 请问我的这个查询语句该怎么优化?
- 配置好Configuration Assistant后,启动OracleOraHome81ManagementServer服务出现奇怪错误!
- 为何装上Oracle 9i Developer Suite后,Enterprise Manager Console就无法启动了?
- 一本比较好的Oracle 8i的DBA图书,市场会接受吗?
- 同一个事务 报“未找到父项关键字” 急
- 帮忙看看怎么写这个存储过程?
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
V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, 1, 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;
RETURN V_RETURN;
END;
/
13:47:57 2 V_COMPARE VARCHAR2(100);
13:47:57 3 V_RETURN VARCHAR2(4000);
13:47:57 4 FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
13:47:57 5 BEGIN
13:47:57 6 RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
13:47:57 7 END;
13:47:57 8 BEGIN
13:47:57 9 V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, 1, 1));
13:47:57 10 IF V_COMPARE >= F_NLSSORT(' 吖 ') AND V_COMPARE <= F_NLSSORT('驁 ') THEN
13:47:57 11 V_RETURN := V_RETURN || 'A';
13:47:57 12 ELSIF V_COMPARE >= F_NLSSORT('八 ') AND V_COMPARE <= F_NLSSORT('簿 ') THEN
13:47:57 13 V_RETURN := V_RETURN || 'B';
13:47:57 14 ELSIF V_COMPARE >= F_NLSSORT('嚓 ') AND V_COMPARE <= F_NLSSORT('錯 ') THEN
13:47:57 15 V_RETURN := V_RETURN || 'C';
13:47:57 16 ELSIF V_COMPARE >= F_NLSSORT('咑 ') AND V_COMPARE <= F_NLSSORT('鵽 ') THEN
13:47:57 17 V_RETURN := V_RETURN || 'D';
13:47:57 18 ELSIF V_COMPARE >= F_NLSSORT('妸 ') AND V_COMPARE <= F_NLSSORT('樲 ') THEN
13:47:57 19 V_RETURN := V_RETURN || 'E';
13:47:57 20 ELSIF V_COMPARE >= F_NLSSORT('发 ') AND V_COMPARE <= F_NLSSORT('猤 ') THEN
13:47:57 21 V_RETURN := V_RETURN || 'F';
13:47:57 22 ELSIF V_COMPARE >= F_NLSSORT('旮 ') AND V_COMPARE <= F_NLSSORT('腂 ') THEN
13:47:57 23 V_RETURN := V_RETURN || 'G';
13:47:57 24 ELSIF V_COMPARE >= F_NLSSORT('妎 ') AND V_COMPARE <= F_NLSSORT('夻 ') THEN
13:47:57 25 V_RETURN := V_RETURN || 'H';
13:47:57 26 ELSIF V_COMPARE >= F_NLSSORT('丌 ') AND V_COMPARE <= F_NLSSORT('攈 ') THEN
13:47:57 27 V_RETURN := V_RETURN || 'J';
13:47:57 28 ELSIF V_COMPARE >= F_NLSSORT('咔 ') AND V_COMPARE <= F_NLSSORT('穒 ') THEN
13:47:57 29 V_RETURN := V_RETURN || 'K';
13:47:57 30 ELSIF V_COMPARE >= F_NLSSORT('垃 ') AND V_COMPARE <= F_NLSSORT('擽 ') THEN
13:47:57 31 V_RETURN := V_RETURN || 'L';
13:47:57 32 ELSIF V_COMPARE >= F_NLSSORT('嘸 ') AND V_COMPARE <= F_NLSSORT('椧 ') THEN
13:47:57 33 V_RETURN := V_RETURN || 'M';
13:47:57 34 ELSIF V_COMPARE >= F_NLSSORT('拏 ') AND V_COMPARE <= F_NLSSORT('瘧 ') THEN
13:47:57 35 V_RETURN := V_RETURN || 'N';
13:47:57 36 ELSIF V_COMPARE >= F_NLSSORT('筽 ') AND V_COMPARE <= F_NLSSORT('漚 ') THEN
13:47:57 37 V_RETURN := V_RETURN || 'O';
13:47:57 38 ELSIF V_COMPARE >= F_NLSSORT('妑 ') AND V_COMPARE <= F_NLSSORT('曝 ') THEN
13:47:57 39 V_RETURN := V_RETURN || 'P';
13:47:57 40 ELSIF V_COMPARE >= F_NLSSORT('七 ') AND V_COMPARE <= F_NLSSORT('裠 ') THEN
13:47:57 41 V_RETURN := V_RETURN || 'Q';
13:47:57 42 ELSIF V_COMPARE >= F_NLSSORT('亽 ') AND V_COMPARE <= F_NLSSORT('鶸 ') THEN
13:47:57 43 V_RETURN := V_RETURN || 'R';
13:47:57 44 ELSIF V_COMPARE >= F_NLSSORT('仨 ') AND V_COMPARE <= F_NLSSORT('蜶 ') THEN
13:47:57 45 V_RETURN := V_RETURN || 'S';
13:47:57 46 ELSIF V_COMPARE >= F_NLSSORT('侤 ') AND V_COMPARE <= F_NLSSORT('籜 ') THEN
13:47:57 47 V_RETURN := V_RETURN || 'T';
13:47:57 48 ELSIF V_COMPARE >= F_NLSSORT('屲 ') AND V_COMPARE <= F_NLSSORT('鶩 ') THEN
13:47:57 49 V_RETURN := V_RETURN || 'W';
13:47:57 50 ELSIF V_COMPARE >= F_NLSSORT('夕 ') AND V_COMPARE <= F_NLSSORT('鑂 ') THEN
13:47:57 51 V_RETURN := V_RETURN || 'X';
13:47:57 52 ELSIF V_COMPARE >= F_NLSSORT('丫 ') AND V_COMPARE <= F_NLSSORT('韻 ') THEN
13:47:57 53 V_RETURN := V_RETURN || 'Y';
13:47:57 54 ELSIF V_COMPARE >= F_NLSSORT('帀 ') AND V_COMPARE <= F_NLSSORT('咗 ') THEN
13:47:57 55 V_RETURN := V_RETURN || 'Z';
13:47:57 56 END IF;
13:47:57 57 RETURN V_RETURN;
13:47:57 58 END;
13:47:57 59 /函数已创建。已用时间: 00: 00: 00.04
13:47:58 scott@TUNGKONG> select F_TRANS_PINYIN_CAPITAL('测试') from dual;F_TRANS_PINYIN_CAPITAL('测试')
----------------------------------------------------------------------------------------------------
C已用时间: 00: 00: 00.01
13:48:27 scott@TUNGKONG> select F_TRANS_PINYIN_CAPITAL('中国') from dual;F_TRANS_PINYIN_CAPITAL('中国')
----------------------------------------------------------------------------------------------------
Z已用时间: 00: 00: 00.01
我上传了一个包,可以获得汉字的拼音或者首字母,我们现在用的,也是以前从网上找到的,非原创
HanZi调用这个
select GetHZPY.GetHzPYCAP('汉字') from dual
HZ
我现在想把‘我们’换成我a表里面的name字段 应该怎么弄啊 谢谢拉
那你直接用
UPDATE a SET name = F_TRANS_PINYIN_CAPITAL('我们')
不就行了。。无非就是个函数的调用。
好像理解错了。应该是 SELECT F_TRANS_PINYIN_CAPITAL(name) FROM a;
长辈(zhangbei)能够识别吗?