POSTGRESQL 與MYSQL 實現分割字串的方法對比

 實現分割字串。
MYSQL版本。 由於MYSQL不支援遞迴，不支援返回表類型的結果，所以代碼比較繁瑣。 我用了兩個函數以及一個預存程序來實現。


-- 得到分割符的總數。

DELIMITER $$CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(f_string VARCHAR(1000),f_delimiter VARCHAR(5)) RETURNS INT(11)BEGIN  -- Get the total number of given string.  RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));END$$DELIMITER ;

-- 得到具體下表的子字元。

DELIMITER $$CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8BEGIN  -- Get the separated number of given string.  DECLARE result VARCHAR(255) DEFAULT '';  SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));  RETURN result;END$$DELIMITER ;

-- 列印結果。 用暫存資料表來實現。

DELIMITER $$CREATE PROCEDURE `sp_print_result`( IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5))BEGIN  -- Get the separated string.  DECLARE cnt INT DEFAULT 0;  DECLARE i INT DEFAULT 0;  SET cnt = func_get_split_string_total(f_string,f_delimiter);  DROP TABLE IF EXISTS tmp_print;  CREATE TEMPORARY TABLE tmp_print (v_text varchar(200) NOT NULL);  WHILE i < cnt  DO    SET i = i + 1;    INSERT INTO tmp_print(v_text) VALUES (func_get_split_string(f_string,f_delimiter,i));  END WHILE;  SELECT * FROM tmp_print;  END$$DELIMITER ;

我們來執行：

CALL sp_print_result('love,you,hate,number',',');query resultv_text love you hate number 

PostgreSQL比較靈活， 有以下幾種方法來實現。
 第一種，普通的分析字串方法。

 create or replace function split_to_string(IN f_string text, IN f_delimiter varchar(10)) returns setof text as$ytt$  declare cnt int;  declare i int;  declare v_result text;  begin      i := 1;      cnt := length(f_string) - length(replace(f_string,f_delimiter,''))+1;      while i <= cnt      loop        v_result := split_part(f_string,f_delimiter,i);return next v_result;        i := i + 1;      end loop;  end;$ytt$ language plpgsql;結果：t_girl=# select split_to_string('love,you,hate,number',',') as result; result -------- love you hate number(4 rows)

第二種， 用自己帶的正則函數來實現。

t_girl=# SELECT ytt FROM regexp_split_to_table('love,you,hate,number', E',+') AS ytt;  ytt   -------- love you hate number(4 rows)t_girl=# 

第三種，用內建的WITH 文法來實現。

t_girl=# with recursive ytt(f1,f2) as (values (0,' '::text) union all select f1+1,split_part('love,you,hate,number',',',f1+1) from ytt where f1 < 20 ) select f2 as result from ytt where f1 >=1 and f1 <= length('love,you,hate,number')-length(replace('love,you,hate,number',',',''))+1; result -------- love you hate number(4 rows)Time: 0.742 ms