Home » Developer & Programmer » Forms » comparing comma separated values (11)
comparing comma separated values [message #653437] Tue, 05 July 2016 09:47 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26749
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
Re: comparing comma separated values [message #653482 is a reply to message #653438] Thu, 07 July 2016 01:24 Go to previous messageGo to next message
Littlefoot
Messages: 21612
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>
Re: comparing comma separated values [message #653819 is a reply to message #653437] Tue, 19 July 2016 08:45 Go to previous messageGo to next message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
It might be so that LISTAGG is of help here. http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 But maybe not...Anyhow...it is a fun function. Smile

[Updated on: Tue, 19 July 2016 08:46]

Report message to a moderator

Re: comparing comma separated values [message #653825 is a reply to message #653819] Tue, 19 July 2016 09:33 Go to previous message
Michel Cadot
Messages: 67467
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

LISTAGG? to convert a string to row? Did you read the documentation you posted?

Previous Topic: Forms 11g, Arabic and numbers
Next Topic: Format Mask for IP
Goto Forum:
  


Current Time: Tue Oct 27 02:20:05 CDT 2020