Home  >  Article  >  Database  >  POSTGRESQL与MYSQL实现分割字符串的方法对比

POSTGRESQL与MYSQL实现分割字符串的方法对比

WBOY
WBOYOriginal
2016-06-07 15:23:191297browse

实现分割字符串。 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) - LENG

实现分割字符串。
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 utf8 BEGIN -- 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 
<pre class="brush:php;toolbar:false">
我们来执行: 
CALL sp_print_result(&#39;love,you,hate,number&#39;,&#39;,&#39;);
query result
v_text 
love 
you 
hate 
number 
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 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,&#39; &#39;::text) 
union all 
select f1+1,split_part(&#39;love,you,hate,number&#39;,&#39;,&#39;,f1+1) from ytt where f1 < 20 
) 
select f2 as result from ytt where f1 >=1 and f1 <= length(&#39;love,you,hate,number&#39;)-length(replace(&#39;love,you,hate,number&#39;,&#39;,&#39;,&#39;&#39;))+1;
 result 
--------
 love
 you
 hate
 number
(4 rows)

Time: 0.742 ms
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn