Bulk Add Commas & Quotes in PL/SQL

1 year ago
Noah Thompson
1 minute

In PL/SQL, loops can be used to add commas and quotes in bulk to strings. Here is an example code:

DECLARE
    input_string VARCHAR2(100) := 'apple,banana,orange';
    output_string VARCHAR2(100);
BEGIN
    FOR i IN 1..LENGTH(input_string) LOOP
        IF SUBSTR(input_string, i, 1) = ',' THEN
            output_string := output_string || ''',' || SUBSTR(input_string, i+1, 1);
        ELSE
            output_string := output_string || SUBSTR(input_string, i, 1);
        END IF;
    END LOOP;

    output_string := '''' || output_string || '''';

    DBMS_OUTPUT.PUT_LINE(output_string);
END;

In this example, we start by defining a string input_string containing commas. Then, we loop through this string and add quotes and commas around any commas we encounter. Finally, we output the new string output_string with quotes added. The output is ‘apple’,’banana’,’orange’.

bannerAds