Oracle extracts Chinese string Pinyin first letter function, pinyin simple code extraction function __ Static function

Source: Internet
Author: User

From the other database to get the function of the pinyin simple code, it is very interesting, the specific idea is, through the Oracle Nlssort function to the Chinese characters in alphabetical order, and then according to the interval of Chinese characters to return the corresponding first letter. The implementation effect and the code are as follows.

I hope I can help you .




/* Get Pinyin simple code function */CREATE OR REPLACE function get_pyjm (p_name in VARCHAR2) return VARCHAR2 as V_compare VARCHAR2 (1
    00);
V_return VARCHAR2 (4000);
            Begin DECLARE 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 (' Acridine ') and V_compare <= f_nlssort (' 驁 ') THEN V_return: = V_return | |
            ' A ';
                elsif v_compare >= f_nlssort (' VIII ') and V_compare <= (' book ') F_nlssort V_return: = V_return | |
            ' B ';
                elsif v_compare >= f_nlssort (' Cha ') and V_compare <= (' wrong ') f_nlssort V_return: = V_return | |
            ' C '; ELSIF v_compare >= f_nlssort (' 咑 ') and V_compare <= (' F_nlssort ') 鵽 V_return: = V_return | |
            ' D ';
                elsif v_compare >= f_nlssort (' 妸 ') and V_compare <= (' F_nlssort ') 樲 V_return: = V_return | |
            ' E ';
                elsif v_compare >= f_nlssort (' FA ') and V_compare <= (' F_nlssort ') 猤 V_return: = V_return | |
            ' F ';
                elsif v_compare >= f_nlssort (' there ') and V_compare <= (' F_nlssort ') hiker V_return: = V_return | |
            ' G ';
                elsif v_compare >= f_nlssort (' 妎 ') and V_compare <= (' F_nlssort ') 夻 V_return: = V_return | |
            ' H ';
                elsif v_compare >= f_nlssort (' no ') and V_compare <= (' F_nlssort ') 攈 V_return: = V_return | |' J ';
                elsif v_compare >= f_nlssort (' ka ') and V_compare <= (' F_nlssort ') 穒 V_return: = V_return | |
            ' K ';
                elsif v_compare >= f_nlssort (' garbage ') and V_compare <= (' F_nlssort ') 擽 V_return: = V_return | |
            ' L ';
                elsif v_compare >= f_nlssort (' 嘸 ') and V_compare <= (' F_nlssort ') 椧 V_return: = V_return | |
            ' M ';
                elsif v_compare >= f_nlssort (' 拏 ') and V_compare <= (' f_nlssort ') malaria V_return: = V_return | |
            ' N ';
                elsif v_compare >= f_nlssort (' 筽 ') and V_compare <= (' F_nlssort ') 漚 V_return: = V_return | |
            ' O ';
                elsif v_compare >= f_nlssort (' 妑 ') and V_compare <= (' exposure ') f_nlssort V_reTURN: = V_return | |
            ' P ';
                elsif v_compare >= f_nlssort (' VII ') and V_compare <= (' F_nlssort ') 裠 V_return: = V_return | |
            ' Q ';
                elsif v_compare >= f_nlssort (' Everybody ') and V_compare <= (' F_nlssort ') 鶸 V_return: = V_return | |
            ' R ';
                elsif v_compare >= f_nlssort (' sa ') and v_compare <= (' F_nlssort ') 蜶 V_return: = V_return | |
            ' S ';
                elsif v_compare >= f_nlssort (' 侤 ') and V_compare <= (' F_nlssort ') 籜 V_return: = V_return | |
            ' T ';
                elsif v_compare >= f_nlssort (' 屲 ') and V_compare <= (' F_nlssort ') 鶩 V_return: = V_return | |
            ' W ';
elsif v_compare >= F_nlssort (' XI ') and V_compare <= (' F_nlssort ') 鑂                V_return: = V_return | |
            ' X ';
                elsif v_compare >= f_nlssort (' ya ') and V_compare <= (' f_nlssort ') rhythmic V_return: = V_return | |
            ' Y ';
                elsif v_compare >= f_nlssort (' 帀 ') and V_compare <= (' Out ') f_nlssort V_return: = V_return | |
            ' Z ';
        End IF;

        End LOOP;
    return v_return;
End;  End;
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.