Flag duplicate characters in a string-CodePudding

I'm using oracle apex and i'm trying to write a pl/sql statement that will flag duplicates in a string. For example the string 'P,T,P,C' has two occurrences of the letter 'P' so it should raise an error. After all my digging the closest I got to achieving this was by using REGEXP_LIKE, but my regular expression skills are sub par. If anyone can assist that would be much appreciated.

DECLARE

    v_seccode varchar2(10) := 'P,T,P,C';

BEGIN

    if regexp_like(v_seccode, '(P{2,}?|C{2,}?|W{2,}?|T{2,}?)') then
        raise_application_error(-20001,'You can not have the same SEC code listed more than once!');
    end if;

END;

CodePudding user response：

As you're using Apex, I suggest you create a validation within it. Code you posted suggests that you'd want to handle that elsewhere (process? Database trigger?).

Presuming that page item name is P1_SECCODE, validation - a PL/SQL function that returns error text - might look like this (read comments within code):

declare
  l_seccode varchar2(20);
begin
  -- Split P1_SECCODE into rows, fetch distinct values and aggregate them back.
  -- If P1_SECCODE = 'P,T,P,C' then the L_SECCODE = 'P,T,C' 
  select listagg(distinct regexp_substr(:P1_SECCODE, '[^,] ', 1, level), ',') 
         within group (order by null) 
  into l_seccode
  from dual
  connect by level <= regexp_count(:P1_SECCODE, ',')   1;
  
  -- Now compare whether P1_SECCODE has the same number of commas as L_SECCODE.
  -- If not, it means that P1_SECCODE contained duplicates so - return an error message
  if regexp_count(:P1_SECCODE, ',') <> regexp_count(l_seccode, ',') then
     return 'You can not have the same SEC code listed more than once!';
  end if;
end;

If your database version doesn't support distinct within listagg, then first fetch distinct values and then aggregate them:

declare
  l_seccode varchar2(20);
begin
  -- Split P1_SECCODE into rows, fetch distinct values and aggregate them back.
  -- If P1_SECCODE = 'P,T,P,C' then the L_SECCODE = 'P,T,C' 
  with temp as
    (select distinct regexp_substr(:P1_SECCODE, '[^,] ', 1, level), ',') val 
     from dual
     connect by level <= regexp_count(:P1_SECCODE, ',')   1
    )
    select listagg(val, ',') within group (order by null)
      into l_seccode
      from temp;
  
  -- Now compare whether P1_SECCODE has the same number of commas as L_SECCODE.
  -- If not, it means that P1_SECCODE contained duplicates so - return an error message
  if regexp_count(:P1_SECCODE, ',') <> regexp_count(l_seccode, ',') then
     return 'You can not have the same SEC code listed more than once!';
  end if;
end;

CodePudding user response：

If your apex version is relatively recent you have access to the APEX_STRING API. Here is some code using that API, using same logic @Littlefoot showed in the other answer:

DECLARE
  l_arr1 apex_t_varchar2;
  l_arr2 apex_t_varchar2;
BEGIN
  -- convert string to array with "," delimiter
  l_arr1 := apex_string.split(:P1_SECCODE,',');
  l_arr2 := l_arr1;
  -- remove dupes from l_arr2
  l_arr2 := l_arr2 MULTISET UNION DISTINCT l_arr2;
  IF l_arr2 != l_arr1 THEN
     return 'You can not have the same SEC code listed more than once!';
  END IF;
END;
/

CodePudding user response：

Given that your string can only have 4 possible letters PCWT, you could just use LIKE expressions along with a regular query:

SELECT *
FROM yourTable
WHERE string NOT LIKE '%P%P%' AND
      string NOT LIKE '%C%C%' AND
      string NOT LIKE '%W%W%' AND
      string NOT LIKE '%T%T%';