1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to use the 'like'operator inside 'in' operator.,

Discussion in 'SQL PL/SQL' started by Vicky, Jun 11, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How can I combine the values retrieved using 'like', inside a 'in' operator.,?!


    I need a alternative for the query below:

    SELECT * FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME like ('%ACC_NO%') and COLUMN_NAME like('%DRAWER_ACCNO_OB%') and column_name..........

    and

    SELECT * FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME in (like ('%NIN_BEN_ACC_NO%'), like('%NHI_DRAWER_ACCNO_OB%'), ...)

    is not working.,.,
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    such syntax
    isn't maintained

    For simple example :
    Code (SQL):

    CREATE TABLE t_your_cols AS
    SELECT 'FILE_MAP_IDX' CN FROM dual UNION ALL
    SELECT 'PARITY_PERIOD' CN FROM dual;

    SELECT * FROM ALL_TAB_COLUMNS tc ,t_your_cols yc
    WHERE instr(COLUMN_NAME, YC.CN) > 0;

     
    if quantity of templates not too big, then it is possible to use here such option :
    Code (SQL):


    SELECT * FROM ALL_TAB_COLUMNS tc
    WHERE regexp_like(COLUMN_NAME, '(FILE_MAP_IDX|PARITY_PERIOD)+?')


     
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Try like this...

    SELECT * FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME like '%NIN_BEN_ACC_NO%'

    union

    SELECT * FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME like '%NHI_DRAWER_ACCNO_OB%'

    Once you get the data export into excel, there you can mark duplicate table names.

    Duplicate table name contains those two columns.

    Hope this will help you....
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why mark them? UNION removes true duplicate entries and a simple ORDER BY 1,2 clause will order the records by table name, thenb column name, collecting all rows containing the same table name together.
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    I am sorry,

    It's UNION ALL not UNION

    SELECT * FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME like '%NIN_BEN_ACC_NO%'

    union all

    SELECT * FROM ALL_TAB_COLUMNS
    WHERE COLUMN_NAME like '%NHI_DRAWER_ACCNO_OB%' ;

    If we use UNION it will throw an error like below.

    ORA-010997: illegal use of LONG datatype
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    He probably doesn't need all of the columns. As Zargon indicated, using UNION and sorting by table and then column will likely provide the information he needs, sorted into a useful order.

    Code (Text):
    SELECT table_name, column_name
    FROM   all_tab_columns
    WHERE  column_name LIKE '%NIN_BEN_ACC_NO%'
    UNION
    SELECT table_name, column_name
    FROM   all_tab_columns
    WHERE  column_name LIKE '%NHI_DRAWER_ACCNO_OB%'
    ORDER BY table_name, column_name;