OraFAQ Forum: Forms » comparing comma separated values

Home » Developer & Programmer » Forms » comparing comma separated values (11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

comparing comma separated values [message #653437]

Tue, 05 July 2016 09:47

sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India

Member

Hi All,

I have i table column with values like ('XYZ','ABC','OPQ','PQR')which is appearing from front end (check box values)
out of which i have few check box values which when selected it should be allowed, for other it shouldn't allow (display error message)
i have issue for third scenario that if 1 value is selected as per my list & other not in my list in this case it should display message
for ex list iam having is 'ABC' & 'PQR' and in check box selected values are 'ABC'&'XYZ'
for this i want to write sql query only, i tried regexp but not getting correct value please suggest on this;

Note values in column may be in any sequence

[Updated on: Tue, 05 July 2016 10:03]

Report message to a moderator

Re: comparing comma separated values [message #653438 is a reply to message #653437]

Tue, 05 July 2016 10:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Report message to a moderator

Re: comparing comma separated values [message #653482 is a reply to message #653438]

Thu, 07 July 2016 01:24

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's an example of how to convert your string into rows you could then use in checking process. See if it helps.

SQL> WITH test AS (SELECT 'XYZ,ABC,OPQ,PQR' col FROM DUAL)
  2      SELECT REGEXP_SUBSTR (col,
  3                            '[^,]+',
  4                            1,
  5                            LEVEL)
  6                result
  7        FROM test
  8  CONNECT BY REGEXP_SUBSTR (col,
  9                            '[^,]+',
 10                            1,
 11                            LEVEL)
 12                IS NOT NULL;

RESULT
---------------
XYZ
ABC
OPQ
PQR

SQL>

Report message to a moderator