用两个定界符分割字符串并转换类型 [英] Split string with two delimiters and convert type

问题描述

我有一个像这样的PL/pgSQL函数(这要归功于实现这一目标的人):

I have a PL/pgSQL function like this (thanks to the guy who made this possible):

 CREATE OR REPLACE FUNCTION public.split_string(text, text)
    RETURNS SETOF text
    LANGUAGE plpgsql
    AS $function$
    DECLARE 
      pos int; 
      delim_length int := length($2);
    BEGIN
      WHILE $1 <> ''
      LOOP
        pos := strpos($1,$2);
        IF pos > 0 THEN
          RETURN NEXT substring($1 FROM 1 FOR pos - 1);
          $1 := substring($1 FROM pos + delim_length);
        ELSE
          RETURN NEXT $1;
          EXIT;
        END IF; 
      END LOOP;
      RETURN;
    END;
    $function$

它使用定界符分割字符串.像这样:

It splits a string with a delimiter. Like this:

select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' ');

"3.584731"
"60.739211,3.590472"
"60.738030,3.592740"
"60.736220"

如何将结果保存在temp_array或temp_table中.因此，我可以在temp_x中获得结果，然后再次拆分这些点.喜欢:

How can I save the results in a temp_array or temp_table. So I can get the the results in temp_x and split up these points again. Like:

"3.584731"
"60.739211"
"3.590472"
"60.738030"
"3.592740"
"60.736220"

，并将值返回为double precision.所有这些都应该在函数中完成.

and return the values as double precision. And all of this should be done in the function.

推荐答案

如果您需要中介步骤:

SELECT unnest(string_to_array(a, ' '))::float8
       -- or do something else with the derived table
FROM   unnest(string_to_array('3.584731 60.739211,3.590472 60.738030', ',')) a;

比 regexp_split_to_table() ，但可能会更快，因为正则表达式通常更昂贵. (使用EXPLAIN ANALYZE进行测试.)

我首先在','分裂，然后在' '-您所描述的颠倒顺序似乎更合适.

I first split at ',', and next at ' ' - the reversed sequence of what you describe seems more adequate.

如果需要，可以将其包装到PL/pgSQL函数中:

If need be, you can wrap this into a PL/pgSQL function:

CREATE OR REPLACE FUNCTION public.split_string(_str text
                                             , _delim1 text = ','
                                             , _delim2 text = ' ')
  RETURNS SETOF float8 AS
$func$
BEGIN
   RETURN QUERY
   SELECT unnest(string_to_array(a, _delim2))::float8
          -- or do something else with the derived table from step 1
   FROM   unnest(string_to_array(_str, _delim1)) a;
END
$func$ LANGUAGE plpgsql IMMUTABLE;

或者只是一个SQL函数:

Or just an SQL function:

CREATE OR REPLACE FUNCTION public.split_string(_str text
                                             , _delim1 text = ','
                                             , _delim2 text = ' ')
  RETURNS SETOF float8 AS
$func$
   SELECT unnest(string_to_array(a, _delim2))::float8
   FROM   unnest(string_to_array(_str, _delim1)) a
$func$ LANGUAGE sql IMMUTABLE;

将其设为IMMUTABLE以允许性能优化和其他用途.

Make it IMMUTABLE to allow performance optimization and other uses.

呼叫(使用_delim1和_delim2提供的默认值):

Call (using the provided defaults for _delim1 and _delim2):

SELECT * FROM split_string('3.584731 60.739211,3.590472 60.738030');

或者:

SELECT * FROM split_string('3.584731 60.739211,3.590472 60.738030', ',', ' ');

最快

为获得最佳性能，请结合 translate() 与unnest(string_to_array(...)):

SELECT unnest(
          string_to_array(
             translate('3.584731 60.739211,3.590472 60.738030', ' ', ',')
           , ','
          )
       )::float8

这篇关于用两个定界符分割字符串并转换类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！