Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Question Unanswered: Select & collate

    Hi experts!

    I need your help, is there any way to change the collation of a resulset which comes from a quer, in SQL you can do this:

    Code:
    SELECT field 1 FROM table COLLATE SQL_Latin1_General_CP1_CI_AS;
    I've been looking a way to do this. Thanks.

    Best regards!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2011
    Posts
    57
    Hi.
    I've already chack it but doesn't work that I need. The issue is that i'm using accents (latin collation) and when I use a substring somtimes i miss a character. Example:

    SUBSTR('país',1,4)

    I get "paí" instead of "país"

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What does your problem with SUBSTR() have to do with collation?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Not COLLATE, its the UNICODE

    It's nothing to do with collate. This is a UNICODE problem.

    Cheer, Bill

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there are options that can be used with substr for this. to avoid cutting a character into half... substr - character based... and not byte based
    have a look in info center
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 recently supports more unicode data(not byte based).

    For example,
    SUBSTRING scalar function

    Code:
    >>-SUBSTRING--(------------------------------------------------->
    
    >--+-expression--FROM--start--+-------------+--USING--+-CODEUNITS16-+-+-><
       |                          '-FOR--length-'         +-CODEUNITS32-+ |   
       |                                                  '-OCTETS------' |   
       '-expression--,--start--+-----------+--,--+-CODEUNITS16-+--)-------'   
                               '-,--length-'     +-CODEUNITS32-+              
                                                 '-OCTETS------'
    SUBSTRING - IBM DB2 9.7 for Linux, UNIX, and Windows

    COLLATION_KEY_BIT - IBM DB2 9.7 for Linux, UNIX, and Windows

  8. #8
    Join Date
    Sep 2011
    Posts
    57
    Hi, I'll try whit a substring, the matter is that not always the same length. And is a message that can change at anytime and could have more than one accent.

    Thanks and best regards.

Posting Permissions

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