Ignore case in SQL select
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Ignore case in SQL select

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Gurus,
    We have and Upper and a Lower function.
    Is there any function or way in which I can issue a select while ignoring the case in a where clause ?

    I do not wish to use 'like'.

    Thanks!

    Hemant

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    You don't need to use "like" for case insensitiveness.
    LOWER and UPPER are the functions for these.
    Is there any reason why you won't want to use these functions?

    - Rajeev

    Rajeev Suri

  3. #3
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Rajeev,
    Suppose I want all names beginning with K or k
    I can say name like 'k%' and name like 'K%'.

    But suppose several other alphabets are present, some in upper and some in lower in the result data set.

    Then how do I get this data?

    Cant I do it through a single select/where?
    Hemant

  4. #4
    Join Date
    Mar 2001
    Posts
    71
    Hi,

    You can create InterMedia index on the column and Use
    CONTAINS in WHERE clause.

    HTH

    np70

  5. #5
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi np70,

    Thanks for the info. Where would I get information on creating an intermedia index? I use 8.1.6

    Thanks again.
    Hemant

  6. #6
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    may be I am missing the point but for your query I would use --

    WHERE UPPER(name) LIKE 'K%'

    This, however will not make use of the index because of the function call on the column itself. But it will return what you want? won't it ?

    - Rajeev

    Rajeev Suri

  7. #7
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Rajeev,

    The point is this.

    Suppose I have two names in a field Name.

    And they are stored as
    KriSHnA
    KriShNa


    I want to select both these in the same query.


    Hemant

  8. #8
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407

    WHERE UPPER(name) LIKE 'KRISHNA%'

    or

    WHERE UPPER(name) = 'KRISHNA'

    Rajeev Suri

  9. #9
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Are you looking for something like this

    select ename from emp where upper(ename) like upper('KriS%');

    here upper fuction will convert everything to upper case

    I Hope this is what you are looking for

    Regards
    Santosh

  10. #10
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi gurus,
    Thanks for the inputs.

    But if you extrapolate the example I gave...say my field has values of

    Krishna
    KRishna
    KRishnA
    kRiSHNa
    KriSHNA
    KRISHNa
    KRISHNA
    KriShNa

    You can imagine how many combinations I have to use.
    Of course as Rajeev mentioned, I could use a combo of lower and upper.
    I just wanted to know if there is any way in which I could do it without using upper and lower.

    Thanks.
    Hemant

Posting Permissions

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


Click Here to Expand Forum to Full Width