[Original] comparison of string Splitting Methods Between POSTGRESQL and MYSQL

Splits strings.

MYSQL version. Because MYSQL does not support recursion and does not support returning results of the table type, the code is cumbersome. I use two functions and a stored procedure.

-- Obtain the total number of delimiters.

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 ;

-- Obtain the sub-characters in the following table.

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 ;

-- Print the result. Use a temporary table.

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 ;

Let's execute:

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

PostgreSQL is flexible and can be implemented in the following ways.

First, a common string analysis method.

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;

Result:

t_girl=# select split_to_string('love,you,hate,number',',') as result; result-------- love you hate number(4 rows)

Second, use the built-in regular functions.

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=#

Third, use the WITH syntax.

t_girl=# with recursive ytt(f1,f2) as (values (0,' '::text)union allselect 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

This article is from "god, let's see it !" Blog, please be sure to keep this source http://yueliangdao0608.blog.51cto.com/397025/1349748