通过与逗号分隔的列表进行比较来选择列值的功能 [英] Function to select column values by comparing against comma separated list

问题描述

我想用逗号分隔的字符串与列值进行比较.

I want to compare my comma delimited string with column values.

我执行了以下操作，但未返回预期结果. ll ='"Java，CPP"'动态地出现.

I did the following, which is not returning the expected result. ll = '"Java,CPP"' is coming dynamically.

create or replace function testing(ll text)
returns void as
$body$
Declare

       foo text[];
       ko text[];
BEGIN
       select unique_code into foo from codings
       where  unique_code = ANY (regexp_split_to_array(ll, '\,'));
       raise info '%',foo;
END;
$body$
Language plpgsql;

我遇到了错误

ERROR:  column "Java,CPP" does not exist
LINE 1: SELECT "Java,CPP"
               ^
QUERY:  SELECT "Java,CPP"
CONTEXT:  PL/pgSQL function testing() line 8 at assignment

ll 的值像上面一样动态变化，现在在我有'Java'和'Cpp'的行中也返回NULL，这两个值都是大小写匹配的.

The value of ll is coming dynamically like above and now it is return NULL also in the row I have 'Java' and 'Cpp', both values in matching case.

select unique_code from codings;

unique_code
  Java
  Python
  CPP
  C
  Haskell

我也尝试过修剪，但是没有用.更新的代码在这里:

I also tried trim but not work. The updated code is here:

create or replace function testing(ll text)
returns void as
$body$
Declare

       foo text[];
       --ll text;
       ko text[];
       oo text;
BEGIN
      --oo := replace(ll,'"','');
      raise info '%',regexp_split_to_array(trim(both '"' from ll), '\,');
      ko := regexp_split_to_array(trim(both '"' from ll), '\,');

       ---ll := "CH-PREP,CH-PRMB";
       --select(regexp_split_to_array(ll, '\|')) into ko;
        --foo := array(select unique_key from membership_map);
       select  unique_code into foo from codings where  unique_code = ANY(ko);
       raise info '%',foo;
       --raise info '%', ko;
END;
$body$
Language plpgsql;

然后:

select testing('"Java，CPP"');

ERROR: malformed array literal: "Java"
DETAIL: Array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function testing(text) line 16 at SQL statement

推荐答案

您需要使用 TRIM 从您的 ll 值中剥离" :

You need to use TRIM to strip the " from your ll value:

select unique_code 
from codings 
where unique_code = ANY (regexp_split_to_array(trim(both '"' from '"Java,CPP"'), '\,'));

示例数据的输出:

unique_code
Java
CPP

SQLFiddle演示

您还有一个问题，就是要在一个语句中分配多个值，为此您需要使用 ARRAY 运算符.更改

You also have an issue in that you are assigning multiple values in one statement, for which you need to use the ARRAY operator. Change

select unique_code into foo from codings where unique_code = ANY(ko);

到

select array(select unique_code from codings where unique_code = ANY(ko)) into foo;

这篇关于通过与逗号分隔的列表进行比较来选择列值的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！