PostgreSQL Oracle 相容性之 - 字元串分割

标簽

PostgreSQL , Oracle , 字元串分割

https://github.com/digoal/blog/blob/master/201804/20180410_02.md#%E8%83%8C%E6%99%AF 背景

Oracle中字元串分割，比較麻煩，可能需要寫PL/SQL來實作。

create or replace TYPE "STRINGS_TABLE" is table of varchar2(2000)  
  
create or replace FUNCTION highsoft_split(  
  p_str       IN long,       --VARCHAR2,  
  p_delimiter IN VARCHAR2)  
RETURN strings_table IS  
  j         INT := 0;  
  i         INT := 1;  
  len       INT := 0;  
  len1      INT := 0;  
  str       long;--VARCHAR2(2000);  
  str_split strings_table := strings_table();  
BEGIN  
  len  := LENGTH(p_str);  
  len1 := LENGTH(p_delimiter);  
  WHILE j < len LOOP  
    j := INSTR(p_str, p_delimiter, i);  
    IF j = 0 THEN  
      j   := len;  
      str := SUBSTR(p_str, i);  
      str_split.EXTEND;  
      str_split(str_split.COUNT) := str;  
      IF i >= len THEN  
        EXIT;  
      END IF;  
    ELSE  
      str := SUBSTR(p_str, i, j - i);  
      i   := j + len1;  
      str_split.EXTEND;  
      str_split(str_split.COUNT) := str;  
    END IF;  
  END LOOP;  
  RETURN str_split;  
END highsoft_split;  
           

效果如下

SQL> select * from table(highsoft_split('abc,d,e,f,g',','));  
  
COLUMN_VALUE  
--------------------------------------------------------------------------------  
abc  
d  
e  
f  
g  
           

https://github.com/digoal/blog/blob/master/201804/20180410_02.md#postgresql-%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%88%86%E5%89%B2 PostgreSQL 字元串分割

PostgreSQL 的字元串處理功能非常強大，已經内置了蠻多可選的分隔函數

List of functions  
   Schema   |         Name          | Result data type |  Argument data types   |  Type    
------------+-----------------------+------------------+------------------------+--------  
 pg_catalog | regexp_match          | text[]           | text, text             | normal  
 pg_catalog | regexp_match          | text[]           | text, text, text       | normal  
 pg_catalog | regexp_matches        | SETOF text[]     | text, text             | normal  
 pg_catalog | regexp_matches        | SETOF text[]     | text, text, text       | normal  
 pg_catalog | regexp_replace        | text             | text, text, text       | normal  
 pg_catalog | regexp_replace        | text             | text, text, text, text | normal  
 pg_catalog | regexp_split_to_array | text[]           | text, text             | normal  
 pg_catalog | regexp_split_to_array | text[]           | text, text, text       | normal  
 pg_catalog | regexp_split_to_table | SETOF text       | text, text             | normal  
 pg_catalog | regexp_split_to_table | SETOF text       | text, text, text       | normal  
 pg_catalog | split_part            | text             | text, text, integer    | normal  
           

效果舉例

postgres=# select regexp_split_to_array('a,b,c,d,e',',');  
 regexp_split_to_array   
-----------------------  
 {a,b,c,d,e}  
(1 row)  
  
postgres=# select regexp_split_to_table('a,b,c,d,e',',');  
 regexp_split_to_table   
-----------------------  
 a  
 b  
 c  
 d  
 e  
(5 rows)  
           

當然，我們也可以通過plpgsql，plpython, plperl, pljava, pllua, 等函數語言來擴充PG的資料庫端計算能力。

https://github.com/digoal/blog/blob/master/201804/20180410_02.md#%E5%8F%82%E8%80%83 參考

https://www.postgresql.org/docs/devel/static/functions-matching.html https://www.postgresql.org/docs/devel/static/functions-string.html