Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    104

    Unanswered: Store value for coulmn in lowercase

    Hi

    We use DB2 8.2.6 on windows 2000.

    Would like to force value of a column in a table to be always stored in lower case. i.e even if some one inserts in mixed case.

    Is there a facility in DB2 to do this?


    Regards

    Harikumar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can use 2 triggers (insert after and update after) to do this. Use the lower function to change the value every time a row is inserted or updated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    I'd suggest to use a BEFORE trigger and set the value to lowercase before the insert or update is performed.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another alternative is to use views and instead-of triggers. Thinking about views, you could also store the data in mixed-case in the table and mask this with a view. Blair Adamache wrote an article about that a while ago.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2006
    Posts
    104
    Thanks for the suggestions. We like the idea of masking with views and may implement that

    Regards

    Harikumar

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I collected a few variations on case-insensitive string operations in DB2 here: http://www.ibm.com/developerworks/db...dm-0712stolze/
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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