PL/SQL逗号分隔列表；去除小块，放入数组 [英] PL/SQL comma delimited list; remove dups and put in array

问题描述

在PL/SQL过程中，我用逗号分隔的列表作为字符串.我需要

I have a comma delimited list as a string in a PL/SQL procedure. I need to

1. 删除重复项
2. 将列表放入数组中.

我发现有多种方法可以做到这两种，但不能同时做到. 有帮助吗?

I have found multiple ways to do either, just not both. Any help?

推荐答案

有一个众所周知的SQL技巧，可以将以逗号分隔的列表转换为行.只需使用该技巧，添加一个DISTINCT关键字，然后将结果BULK COLLECT放入您的数组中(我假设您是说集合).

There is a well-known SQL trick for turning comma-separated lists into rows. Just use that trick, add a DISTINCT keyword, and BULK COLLECT the results into your array (I assume you mean collection).

DECLARE
  p_test_string   VARCHAR2 (4000) := 'A,B,C,B,B,D';

  TYPE string_array_type IS TABLE OF VARCHAR2 (4000);

  l_array         string_array_type;
BEGIN
  SELECT DISTINCT REGEXP_SUBSTR (p_test_string,
                        '[^,]+',
                        1,
                        LEVEL)
  BULK   COLLECT INTO l_array
  FROM   DUAL
  CONNECT BY REGEXP_SUBSTR (p_test_string,
                            '[^,]+',
                            1,
                            LEVEL)
               IS NOT NULL
  ORDER BY 1;

  DBMS_OUTPUT.put_line ('l_array.count = ' || l_array.COUNT);
  DBMS_OUTPUT.put_line ('l_array(2) = ' || l_array (2));
END;

输出:

l_array.count = 4
l_array(2) = B

这篇关于PL/SQL逗号分隔列表；去除小块，放入数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！