Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Unanswered: like any or like all

    Some websites indicate that there should be a like any or like all condition in oracle sql, but when I try a very basic version of that in sql navigator it does not work. Does anyone know of another way of doing this? I mean without doing a bunch of or statements.

    Example

    Code:
    select *
    from all_tab_columns
    where column_name like any ( 'S%', 'T%')
    ;
    When I run this I get ORA-00936: missing expression

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Some websites indicate that there should be a like any or like all condition in oracle sql,
    URL?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by p1px
    Some websites indicate that there should be a like any or like all condition in oracle sql
    Did you check the manual?
    That is the only website that contains the truth

  4. #4
    Join Date
    Aug 2007
    Posts
    10
    I will look for another URL, but the question is really whether anyone knows a way of doing this.

    Here is an IBM example, though It may not be for oracle I guess:

    http://publib.boulder.ibm.com/infoce.../BGBBJHJI.html
    Last edited by p1px; 06-22-09 at 13:33.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the question is really whether anyone knows a way of doing this
    THIS?

    When all else fails, Read The Fine Manual

    Pattern-matching Conditions
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2007
    Posts
    10
    Sorry, again. By this I meant to find a way to combine the LIKE and ANY keywords. There may be a keyword that I am not aware of which of course makes it hard to search the manual.

    I did look at your link, thank you, but this just tells me that if there is a way to do what I want, it must be done differently.

    The idea being a way to see if a value is like (but not equal to) any of a set of other values. If I was checking equal to the following would work:

    Code:
    select *
    from all_tab_columns
    where column_name =any ('OWNER', 'TABLE_NAME' )
    Or you could use "IN". Basically I want to replace the = with a LIKE, if anyone knows how to do this.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select *
    from all_tab_columns
    where column_name in ('OWNER', 'TABLE_NAME' )

    However, the statement

    select *
    from all_tab_columns
    where column_name in ('OWNER%', '%TABLE_NAME' )

    is illegal and would have to be written as

    select *
    from all_tab_columns
    where (column_name like 'OWNER%' or column_name like '%TABLE_NAME' )
    Last edited by beilstwh; 06-22-09 at 14:47.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Nov 2003
    Posts
    76
    where (column_name like 'OWNER%' or column_name like '%TABLE_NAME' )

    I think he is aware of that Bill, He is looking to see if this is possible:

    select *
    from all_tab_columns
    where column_name like ( 'OWNER%' , '%TABLE_NAME' )

    Personally I dont think its possible, perhaps Oralce has something new to address this which I am not familiar with (no surprise there). I would be very interested in learning about this also though.

  9. #9
    Join Date
    Aug 2007
    Posts
    10
    Thanks naweed, that is exactly what I mean. If it is possible.

    OR statements work, but not well if there is a large number of possibilities. They can quickly make what should be a simple sql statement verfy complex and hard to read.

  10. #10
    Join Date
    Nov 2003
    Posts
    76
    Got it!!

    Try this, you will find any table name which has a combo like (BI or BO or SP) in its name. I gues you'll have to play around with this. The key is the parrallel bar which acts like 'or'.

    Thanks for the manual hint/link anacedent. I am so excited I am going to do a situp.

    SELECT * from all_tables where owner = 'RDBP03_APSR'
    and REGEXP_LIKE (table_name, '(BI|BO|SP)', 'i');

  11. #11
    Join Date
    Aug 2007
    Posts
    10
    Beautiful. That will do well. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •