split a string into individual elements in PL/SQL-CodePudding

This is for PL/SQL

I am being passed in a series of strings, delimited by a comma, in a string. I would like to take those delimited strings and create an IN clause within my query.

I was thinking a simply add an open parenthesis and single quote to the front and, at the end of the string and single quote with a closing parenthesis, trim the spaces, and swap the commas out with a quote comma quote string. the problem falls when the string has an embedded space in the delimited string.

For example, the string that is being passed in is

TNR, abc, D N, w

What i am looking to receive after processing it would be

('TNR','ABC','D N','W')

I'm not sure where i should begin.

CodePudding user response：

What you described is way too complicated. Just split the string into rows and use them in an IN clause as a subquery.

This is example that runs in SQL*Plus (hence single quotes and && for the substitution variable); query returns one row (from the dual table) as its dummy column contains letter X which is part of the input string:

SQL> SELECT *
  2    FROM DUAL
  3   WHERE dummy IN (    SELECT TRIM (REGEXP_SUBSTR ('&&par_string',
  4                                                   '[^,] ',
  5                                                   1,
  6                                                   LEVEL))
  7                         FROM DUAL
  8                   CONNECT BY LEVEL <= REGEXP_COUNT ('&&par_string', ',')   1);
Enter value for par_string: TNR, abc, D N,X, w

D
-
X

SQL>

Subquery itself returns

SQL>     SELECT TRIM (REGEXP_SUBSTR ('&&par_string',
  2                                  '[^,] ',
  3                                  1,
  4                                  LEVEL))
  5        FROM DUAL
  6  CONNECT BY LEVEL <= REGEXP_COUNT ('&&par_string', ',')   1;
Enter value for par_string: TNR, abc, D N,X, w

TRIM(REGEXP_SUBSTR
------------------
TNR
abc
D N
X
w

SQL>

If you use bind variable, you'd use e.g. :par_string instead of '&&par_string'; or, if it was passed to a procedure or a function, you'd just just the parameter name - par_string.

CodePudding user response：

Use REPLACE to change , into ', ' and then prepend (' and append '):

DECLARE
  list VARCHAR2(50) := 'TNR, abc, D N, w';
BEGIN
  list := '(''' || REPLACE(list, ', ', ''', ''') || ''')';
  DBMS_OUTPUT.PUT_LINE(list);
END;
/

If the white space after the comma is optional then you can use REGEXP_REPLACE rather than REPLACE:

DECLARE
  list VARCHAR2(50) := 'TNR,abc, D N,    w';
BEGIN
  list := '(''' || REGEXP_REPLACE(list, ',\s*', ''', ''') || ''')';
  DBMS_OUTPUT.PUT_LINE(list);
END;
/

Which both output:

('TNR', 'abc', 'D N', 'w')

db<>fiddle here

CodePudding user response：

if you are using 10g or letter, I believe you can user regex_substr.