CODEWITHSUNDEEP
oracle-databaseplsql
Andrei Maieras
Andrei Maieras

Reputation: 706

Dynamic sql for IN clause pl-sql

I have a situation where i'm builduing an sql in a string and somewhere in the sql string i have this WHEREclause with AND :

ls_sql := ls_sql ||   
                    'and a.name IN( SELECT regexp_substr(''' || ls_activity_config || ''', ''' ||  lc_form || ''',' || 1 || ',' ||' level) FROM DUAL ';

Don't mind the regexp_substr method. My problem is that ls_activity_config is an array of string ('a','b','c') and a.name column can have a value of 'a' or 'a-#1' or 'a-#2' and so on. What I want to do is to match even these last values with '-#no' at the end.

What I've tried:

SUBSTR(a.name,1,INSTR(a.name,'-#',1) - 1);

This works when the a.name column has the value with '-#no' in it but won't work when the value doesn't contain it. That's normal because INSTR function will return 0. Any ideas on how to make this work for both values (with '-#no' or without it at the end)??

I was able to find a simpler solution 

NVL(SUBSTR(a.name, 1, instr(a.name,' || '''-#''' || ',' || '1)-1), a.name)

Thank you

Upvotes: 1

Views: 594

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Am I over-simplifying this or don't you just want a REGEXP_REPLACE() call around a.name that keeps only the first part of a.name up to but not including the optional "-#<digit>"? The various examples below illustrate this. All that instr/substr stuff gives me a headache (granted regular expressions can give their share of headaches too, heh).

SQL> with a(name) as (
     select 'a'      from dual union
     select 'a-#3'   from dual union
     select 'b'      from dual union
     select 'c'      from dual union
     select 'efs'    from dual union
     select 'efs-#4' from dual
   )
   select regexp_replace(name, '^(.*)?(-#\d)', '\1') "a.name"
   from a;

a.name
-----------------------------------------------------------------
a
a
b
c
efs
efs

6 rows selected.

SQL>

EDIT: I realized after adding some more test data that REGEXP_REPLACE returns the original string if the match criteria is not found, which could return unexpected results. So, I reworked things to include a where clause that ensures a.name only has the expected values (a single lowercase letter followed by the end of the line or an optional dash) in the first place before testing against the array of values. So, make sure a.name is in the format you expect before using it in your test. Anyway this goes to show once again, "always expect the unexpected" and test for it! 

SQL> with a(name) as (
        select 'a;'     from dual union
        select 'a-#3'   from dual union
        select 'a'      from dual union
        select 'b'      from dual union
        select 'c'      from dual union
        select 'efs'    from dual union
        select 'efs-#4' from dual
      )
      select regexp_substr(name, '^([a-z])($|?-)', 1, 1, NULL, 1) "a.name"
      from a
      where regexp_like(name, '^[a-z]($|?-)');

a.name
------
a
a
b
c

SQL>

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

    So to better illustrate your problem. Check below example.

    SELECT
      CASE
        WHEN INSTR(A.NM,'-',1) > 0
        THEN SUBSTR(a.NM,1,INSTR(a.NM,'-',1) - 1)
        ELSE A.NM
      END no#
    FROM
      (SELECT 'a-#1' nm FROM dual
      UNION ALL
      SELECT 'a-#2' nm FROM dual
      UNION ALL
      SELECT 'a-#3' nm FROM dual
      UNION ALL
      SELECT 'a' nm FROM dual
      )A;


----------------Modified query for further requirements-------------------------

SELECT b.name
FROM homs.t_objects b,
  (SELECT DISTINCT
    CASE
      WHEN INSTR(A.NM,'-',1) > 0
      THEN SUBSTR(a.NM,1,INSTR(a.NM,'-',1) - 1)
      ELSE A.NM
    END no#
  FROM
    (SELECT 'a-#1' nm FROM dual
    UNION ALL
    SELECT 'a-#2' nm FROM dual
    UNION ALL
    SELECT 'b-#3' nm FROM dual
    UNION ALL
    SELECT 'a' nm FROM dual
    )A
  )
WHERE b.name LIKE '%'||a.NO#||'%';

Upvotes: 1

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

Here is the sample query:

select case when a.name like '%-%' then substr(a.name, 1, instr(a.name, '-', 1)-1)
else a.name end from table_name;

We are searching if the string contains '-' in it. If there is '-' query returns string before '-' else it will return actual string it self.

Hope this helps.

Upvotes: 1

Related Questions