db2

Ashok

10y1.1kSQL

i need script in db2

i declared a variable IN pcontact varchar(50)

my requirement is if i enter partial value[ex:npn11] i need to get records based on partial value else if i entered full values with comma

seperated[more than one value ex:npn1101,npn1102]

i need to get records how to achieve this

ex:

---

IF pcontact <> '' THEN

SET pcontact = UPPER ( pcontact ) ;

SET where column IN (' || TRIM ( pcontact ) || ')' ;

END IF ; this is what we r using in current system so i need to implement both partil as well as comma seperated

Thanks

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi1y ago

You can use a combination of string manipulation and dynamic SQL. Below is a script that demonstrates how to handle both cases:

-- Declare the input variable
DECLARE pcontact VARCHAR(50);
SET pcontact = 'npn11'; -- Example input, can be partial or comma-separated

-- Declare a variable to hold the SQL query
DECLARE sql_stmt VARCHAR(1000);

-- Check if the input contains a comma
IF POSITION(',' IN pcontact) = 0 THEN
    -- Partial match case
    SET sql_stmt = 'SELECT * FROM your_table WHERE UPPER(column) LIKE ''%' || UPPER(TRIM(pcontact)) || '%''';
ELSE
    -- Comma-separated values case
    SET sql_stmt = 'SELECT * FROM your_table WHERE UPPER(column) IN (''' || REPLACE(UPPER(TRIM(pcontact)), ',', ''',''') || ''')';
END IF;

-- Execute the dynamic SQL
EXECUTE IMMEDIATE sql_stmt;