Results 1 to 6 of 6

Thread: IN range check

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: IN range check

    Does anyone know why I can't use the % wild card character with an IN statement? select * from my_table where name in ('Ste%','Bil%')
    doesn't return any results even they are in the table. Just wondered, thanks. I am running Server 2000.

  2. #2
    Join Date
    May 2003
    Posts
    26
    select * from my_table where name in ('Ste%','Bil%')
    From your example, in order for any records to be returned, 'name' would have to be compared against the values in the IN clause with at least one of the comparisons evaluating as TRUE.

    Strings containing wildcards will never evaluate as equal when compared with another string, you can only compare them using LIKE.

    The following query is doing the same thing as yours above, just written a little differently, it would also return 0 rows regardles of the data.

    SELECT * FROM my_table where name = 'Ste%' OR name = 'Bil%'

  3. #3
    Join Date
    Dec 2003
    Posts
    19

    Re: IN range check

    wildcards (% and _) are interpreted for the operator LIKE, not operator IN thus you cannot use wildcards in an IN expresion, you are limited to use subselects

    from BOL,

    test_expression [ NOT ] IN
    (
    subquery
    | expression [ ,...n ]
    )

  4. #4
    Join Date
    Aug 2003
    Posts
    328

    Re: IN range check

    Ok. Thanks. I guess I was expecting wildcards to work the same way as in SQL for Oracle.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where name in ('Ste%','Bil%') works in oracle???????

    i am most skeptical

    i don't have oracle, so could can someone who does please run a quick test and confirm this?



    rudy
    http://r937.com/

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    You are quite correct. I have Oracle too and the % wildcard does not work with IN. My mistake.

Posting Permissions

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