CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Cody Maxie
Cody Maxie

Reputation: 113

Oracle SQL - If value is a substring of any string in multi-select parameter

Say you have a parameter :p_companys that is a list of values that is multi-selectable:

-----------
| company |
-----------
| 50 - ar |
| 55 - ag |
| 62 - hh |
| 70 - nr |
-----------

Now say from this list a user selects 50 - ar and 55 - ag as their values. The parameter is now a list of those two values.

In our SQL query we're using the company number and checking to see if they're contained in this parameter. The company number is the first 2 digits (50 and 55.) Is there a way to do something like this:

select comp -- 50 and 55
from companies
where comp in :p_companys

but instead of comparing to the entire string throughout the list we just compare to the first x amount of characters (essential taking a sub string of all values in the parameter?)

Upvotes: 0

Views: 777

Answers (3)

Cody Maxie
Cody Maxie

Reputation: 113

Okay so it turns out you don't have to use just a single row in Oracle when dealing with lists. You can use two rows, and I've found that in doing so the second row is treated as the value that is passed to SQL. The first row is treated as the value shown to the users. So you could have a list like this:

---------------------------
|    comp_desc   |  comp  |
---------------------------
|  50 - retail   |   50   |
 . . . . . . . . . . . . .
|  90 - lodging  |   90   |

Where the user will see column comp_desc values passed in the menu select, but the SQL query will be given the comp column's values via the :p_company value.

So using this setup we can simply use where comp in (:p_company) (note that the parenthesis is needed here)

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Assuming you have an undetermined number of parameters in your input selection, I think you need to use a combination of regexp and connect by level

Something like this should do the trick

with t as 
(
SELECT regexp_substr(:p_companys ,'[^,]+',1,level) as mycol FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(:p_companys ,'[,]')+1
) select comp from companies
where comp in ( select mycol from t ) 
;

Example

SQL> create table t ( c1 varchar2(1) ) ;

Table created.

SQL> insert into t values ( 'A' );

1 row created.

SQL> insert into t values ( 'B' );

1 row created.

SQL> insert into t values ( 'C' );

1 row created.

SQL> select * from t ;

C
-
A
B
C

Now, let's test when the input variable is a list of strings

with x as
    (
    SELECT regexp_substr('A,B,C' ,'[^,]+',1,level) as mycol FROM dual
    CONNECT BY LEVEL <= REGEXP_COUNT('A,B,C' ,'[,]')+1
   ) select * from t where c1 in  ( select mycol from x )
   ;

C
-
A
B
C

Upvotes: 1

Andones
Andones

Reputation: 1

You need to put it in WHERE clause.

If you expect 2 digits you can use regexp like this: REGEXP_SUBSTR(comp, '\d\d') in ('50', '55')

Or if you just want to cut from your string the first 2 characters: SUBSTR(comp, 1, 2) in ('50', '55')

Upvotes: 0

Related Questions