How to: case insensitive search of enduser attribute values - firstname

Version 1
    This document was generated from CDN thread

    Created by: Dan Coffey on 29-06-2009 12:44:39 PM
    I have a requirement to search LDAP for user data.
    The search needs to be case insensitive.
    For example: the LDAP value is: <endusr_firstname>Walt</endusr_firstname>
    the search could be for the first name ("Walt") or the search request could be ("walt") or ("w")
    the SQL:
    WHERE endusr.firstname LIKE '%W%' " returns <endusr_firstname>Walt</endusr_firstname>
    but
    WHERE endusr.firstname LIKE '%w%' " returns a group of users with lower case "w" in the first name.
    <endusr_firstname>Andrew</endusr_firstname>
    <endusr_firstname>Bishwa</endusr_firstname>
    etc.
    I want to return all firstname's that have "W" or "w"
     
    Does anyone have a sugestion how this can be acomplished.
     
    Thanks,
     
    Dan

    Subject: RE: How to: case insensitive search of enduser attribute values - firstname
    Replied by: David Staudt on 29-06-2009 06:47:14 PM
    Can you clarify whether this will queried against LDAP (UCM versions 4.x) or via SQL? (UCM versions 5.x+)?
     
    If you are using AXL via UCM5+, then you will need to take into account the underlying database Informix/IDX (if you are using AXL, then you are querying a SQL database, even if the database is synched periodically with a company LDAP.)  A quick internet search had a hint here, I'm sure you can find additional discussions online: http://www.geocities.com/SiliconValley/Bridge/4578/faq.html (note only options 1 and 2 for the case-insensitive search topic make sense.)

    Subject: RE: How to: case insensitive search of enduser attribute values - firstname
    Replied by: Dan Coffey on 29-06-2009 09:53:56 PM
    David,
     
    I'm using UCM 7.x
    Thanks for the database information.  I've looked at the link you sited and at the Informix manuals.
    There are a limited set of functions UPPER, LOWER and INITCAP that the manual specify for case-insensitive searchs.
    I'll have to make the user's aware that the searchs will be case-sensitive.
     
    This can be closed.
     
    Thanks again,
     
    Dan

    Subject: RE: How to: case insensitive search of enduser attribute values - firstname
    Replied by: Stephan Steiner on 06-07-2009 09:43:29 AM
    I've used this myself..
     
    WHERE LOWER(endusr.firstname) LIKE '%w%' "
     
    and call toLower or whatever the function is called in your programming language for the search term.. the result will still be case sensitive but the search won't be (unless you do something like SELECT LOWER(enduser.userid), ... FROM enduser WHERE ...)