获取表中以某个拼音开头的记录

今天在在论坛上有个帖子里的回帖中一个朋友贴出了一个不错的函数，如果记录中有中文的话，通过使用这个函数，可以比较容易的判别出哪些记录是以某个特定首字母拼音开头的。

 

函数如下

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
    IF UPPER(SUBSTR(P_NAME,1,1))>='A' AND UPPER(SUBSTR(P_NAME,1,1))<='Z' THEN
        RETURN SUBSTR(P_NAME,1,1);
    END IF;
    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;
/

以后只要
select * from table

where F_TRANS_PINYIN_CAPITAL(name) = 'A' order by NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M')
就能找到所有以A开头且按拼音排序的咯。

下面做个例子看看

 

SQL> select * from t3;

NAME
----------------------------------------
安
被
哎

SQL> CREATE OR REPLACE FUNCTION F_TRANS_PINYIN_CAPITAL(
  2      P_NAME IN VARCHAR2
  3  )RETURN VARCHAR2 AS
  4  V_COMPARE VARCHAR2(100);
  5  V_RETURN VARCHAR2(4000);
  6  FUNCTION F_NLSSORT(
  7      P_WORD IN VARCHAR2
  8  )RETURN VARCHAR2 AS
  9  BEGIN
 10      RETURN NLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M');
 11  END;
 12  BEGIN
 13      IF UPPER(SUBSTR(P_NAME,1,1))>='A' AND UPPER(SUBSTR(P_NAME,1,1))<='Z' TH
EN
 14          RETURN SUBSTR(P_NAME,1,1);
 15      END IF;
 16      V_COMPARE := F_NLSSORT(SUBSTR(P_NAME,1,1));
 17      IF V_COMPARE>= F_NLSSORT('吖')AND V_COMPARE<= F_NLSSORT('驁')THEN
 18          V_RETURN := V_RETURN||'A';
 19      ELSIF V_COMPARE>= F_NLSSORT('八')AND V_COMPARE<= F_NLSSORT('簿')THEN
 20          V_RETURN := V_RETURN||'B';
 21      ELSIF V_COMPARE>= F_NLSSORT('嚓')AND V_COMPARE<= F_NLSSORT('錯')THEN
 22          V_RETURN := V_RETURN||'C';
 23      ELSIF V_COMPARE>= F_NLSSORT('咑')AND V_COMPARE<= F_NLSSORT('鵽')THEN
 24          V_RETURN := V_RETURN||'D';
 25      ELSIF V_COMPARE>= F_NLSSORT('妸')AND V_COMPARE<= F_NLSSORT('樲')THEN
 26          V_RETURN := V_RETURN||'E';
 27      ELSIF V_COMPARE>= F_NLSSORT('发')AND V_COMPARE<= F_NLSSORT('猤')THEN
 28          V_RETURN := V_RETURN||'F';
 29      ELSIF V_COMPARE>= F_NLSSORT('旮')AND V_COMPARE<= F_NLSSORT('腂')THEN
 30          V_RETURN := V_RETURN||'G';
 31      ELSIF V_COMPARE>= F_NLSSORT('妎')AND V_COMPARE<= F_NLSSORT('夻')THEN
 32          V_RETURN := V_RETURN||'H';
 33      ELSIF V_COMPARE>= F_NLSSORT('丌')AND V_COMPARE<= F_NLSSORT('攈')THEN
 34          V_RETURN := V_RETURN||'J';
 35      ELSIF V_COMPARE>= F_NLSSORT('咔')AND V_COMPARE<= F_NLSSORT('穒')THEN
 36          V_RETURN := V_RETURN||'K';
 37      ELSIF V_COMPARE>= F_NLSSORT('垃')AND V_COMPARE<= F_NLSSORT('擽')THEN
 38          V_RETURN := V_RETURN||'L';
 39      ELSIF V_COMPARE>= F_NLSSORT('嘸')AND V_COMPARE<= F_NLSSORT('椧')THEN
 40          V_RETURN := V_RETURN||'M';
 41      ELSIF V_COMPARE>= F_NLSSORT('拏')AND V_COMPARE<= F_NLSSORT('瘧')THEN
 42          V_RETURN := V_RETURN||'N';
 43      ELSIF V_COMPARE>= F_NLSSORT('筽')AND V_COMPARE<= F_NLSSORT('漚')THEN
 44          V_RETURN := V_RETURN||'O';
 45      ELSIF V_COMPARE>= F_NLSSORT('妑')AND V_COMPARE<= F_NLSSORT('曝')THEN
 46          V_RETURN := V_RETURN||'P';
 47      ELSIF V_COMPARE>= F_NLSSORT('七')AND V_COMPARE<= F_NLSSORT('裠')THEN
 48          V_RETURN := V_RETURN||'Q';
 49      ELSIF V_COMPARE>= F_NLSSORT('亽')AND V_COMPARE<= F_NLSSORT('鶸')THEN
 50          V_RETURN := V_RETURN||'R';
 51      ELSIF V_COMPARE>= F_NLSSORT('仨')AND V_COMPARE<= F_NLSSORT('蜶')THEN
 52          V_RETURN := V_RETURN||'S';
 53      ELSIF V_COMPARE>= F_NLSSORT('侤')AND V_COMPARE<= F_NLSSORT('籜')THEN
 54          V_RETURN := V_RETURN||'T';
 55      ELSIF V_COMPARE>= F_NLSSORT('屲')AND V_COMPARE<= F_NLSSORT('鶩')THEN
 56          V_RETURN := V_RETURN||'W';
 57      ELSIF V_COMPARE>= F_NLSSORT('夕')AND V_COMPARE<= F_NLSSORT('鑂')THEN
 58          V_RETURN := V_RETURN||'X';
 59      ELSIF V_COMPARE>= F_NLSSORT('丫')AND V_COMPARE<= F_NLSSORT('韻')THEN
 60          V_RETURN := V_RETURN||'Y';
 61      ELSIF V_COMPARE>= F_NLSSORT('帀')AND V_COMPARE<= F_NLSSORT('咗')THEN
 62          V_RETURN := V_RETURN||'Z';
 63      END IF;
 64
 65      RETURN V_RETURN;
 66  END;
 67  /

函数已创建。

SQL> select * from t3
  2  where F_TRANS_PINYIN_CAPITAL(name) = 'A'
  3  order by NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M');

NAME
----------------------------------------
哎
安

SQL> select * from t3
  2  where F_TRANS_PINYIN_CAPITAL(name) = 'B'
  3  order by NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M');

NAME
----------------------------------------
被

 

再多加两条记录试试

SQL> insert into t3 values('李');

已创建 1 行。

SQL> insert into t3 values('王');

已创建 1 行。

SQL> commit;

SQL> select * from t3
  2  where F_TRANS_PINYIN_CAPITAL(name) = 'L'
  3  order by NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M');

NAME
----------------------------------------
李

SQL> select * from t3
  2  where F_TRANS_PINYIN_CAPITAL(name) = 'W'
  3  order by NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M');

NAME
----------------------------------------
王