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 || Substr(p_Name, i, 1);
End If;
End Loop;
Return v_Return;
End;
--------------------------------------Test
Select Upper(f_Trans_Pinyin_Capital('中华人民共和国')) From Dual
--Or
Declare
Text Varchar(10);
Begin
Text := f_Trans_Pinyin_Capital('中华人民共和国');
Dbms_Output.Put_Line(Text);
End;