PostgreSQL 字符串拆分与合并案例

with person_name as (

   select c.id,

       array_to_string(array_agg( distinct p.c_name), ' , ' ) as p_name

   from biz_notification_config c

        join biz_notification_person p

          on p.id =

            any (string_to_array(c.persons, ',' ):: int [])

   group by c.id

),

    group_name as (

      select c.id,

         array_to_string(array_agg( distinct g.c_name), ' , ' ) as g_name

      from biz_notification_config c

          join biz_notification_group g

             on g.id = any (string_to_array(c.c_groups, ',' ):: int [])

      group by c.id

    )

select config.*, person_name.p_name, group_name.g_name

from biz_notification_config config

      left join person_name

           on config.id = person_name.id

      left join group_name

           on config.id = group_name.id;

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

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