PostgreSQL Oracle 兼容性之 - 字符串分割

背景

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  

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://www.postgresql.org/docs/devel/static/functions-matching.html

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

digoal’s 大量PostgreSQL文章入口