Can some one help me compare 2nd data to 1st data by a comma delimeter value PL SQL

Transact-SQL
1

Input 1: DBA, TESTER, DEVELOPER
Input 2: DBA, DEVELOPER, TESTING

Result: DBA, TESTER, DEVELOPER, TESTING

  • order will be based on input 1 and will return a unique value.
2

You can do something like this:

DECLARE @Input1 VARCHAR(100) = 'DBA,TESTER,DEVELOPER'
DECLARE @Input2 VARCHAR(100) = 'DBA,DEVELOPER,TESTING';

WITH Inputs ( [Position] ) AS (
	SELECT value
	FROM STRING_SPLIT(@Input1, ',') A 
	UNION
	SELECT value
	FROM STRING_SPLIT(@Input2, ',') B
)
SELECT STRING_AGG([Position],',')
FROM [Inputs];

The output will be as follows:

| Result                       |
|------------------------------|
| DBA,DEVELOPER,TESTER,TESTING |
3

I got compilation error in SQL Dev in this PL SQL

4

image

You might consider changing the post tag to oracle.
Most of the people here are MS SQL Server users, but some also know Oracle.

5

I missed the part that you were looking for a PL-SQL solution. The solution I provided was for SQL Server.

6

CREATE OR REPLACE FUNCTION compare_comma_delimited(
first_data IN VARCHAR2,
second_data IN VARCHAR2
) RETURN VARCHAR2 AS
v_first_list DBMS_UTILITY.lname_array;
v_second_list DBMS_UTILITY.lname_array;
v_diff VARCHAR2(4000);
v_cnt1 PLS_INTEGER;
v_cnt2 PLS_INTEGER;
BEGIN
-- Convert the comma-delimited strings into arrays
DBMS_UTILITY.comma_to_table(first_data, v_cnt1, v_first_list);
DBMS_UTILITY.comma_to_table(second_data, v_cnt2, v_second_list);

-- Initialize the difference string
v_diff := '';

-- Compare the two lists
FOR i IN 1..v_cnt1 LOOP
    IF i > v_cnt2 OR v_first_list(i) != v_second_list(i) THEN
        v_diff := v_diff || 'Difference at position ' || i || ': ' || v_first_list(i) || ' vs ' || NVL(v_second_list(i), 'NULL') || CHR(10);
    END IF;
END LOOP;

-- Check if the second list is longer than the first
IF v_cnt2 > v_cnt1 THEN
    FOR i IN (v_cnt1 + 1)..v_cnt2 LOOP
        v_diff := v_diff || 'Difference at position ' || i || ': NULL vs ' || v_second_list(i) || CHR(10);
    END LOOP;
END IF;

-- Return the difference, or 'No differences' if there are none
RETURN CASE
           WHEN v_diff IS NULL OR v_diff = '' THEN 'No differences'
           ELSE v_diff
       END;

END compare_comma_delimited;

tinyurl .com/ynyuwnps