Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009

    Unanswered: sorting in asc but alpha's should come before numerics

    Hi All
    Please check with the following query. I need the solution in this very soon .

    Query :
    select case when length(c_fam_extndd) > 3 then c_fam_extndd else c_fam_extndd|| ' ' ||x_fam_extndd end as fam from fcfm_npi.vpm_tc_trnsn order by fam with ur

    Problem we are facing:
    We want sort should be like alphabets first then digits in asc.
    By sort we are getting following list of vals which should be sorted like alpha’s first then digits but in asc.

    please reply me soon

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    You could create your database with a manually defined collation sequence (an example is described here: Case-insensitive string comparisons with DB2 for Linux, UNIX, and Windows). Alternatively, you could create a UDF that shuffles the code points in the desired way, i.e. iterate of the bytes/characters in the string and replace each byte in such a way that the desired ordering is accomplished. Then you can do:
    SELECT ...
    FROM ...
    ORDER BY sort_udf(c_fam_extndd)
    If your string contains only digits or only letters (but no mixture), you could use a CASE expression working on the first character. That would introduce a more significant sort criteria, i.e. letters map to 0 and digits to 1, implying that digits are sorted after letters.
    SELECT ...
    FROM ...
    ORDER BY CASE SUBSTR(c_fam_extndd, 1, 1) BETWEEN '0' AND '9' THEN 1 ELSE 0 END, c_fam_extndd
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    If your string contains only alphabets('a'-'z' and 'A'-'Z'), numeric digits('0'-'9') and blanks(' '),
    here are another examples.

    1) If you want to change collating sequence...
           TRANSLATE( fam
                    , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
                    , 'aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ0123456789'
                    ) ASC
    2) If you want position all numeric strings after alphabet strings or mixed strings...
           ISNUMERIC(fam) ASC
         , fam ASC
    Where ISNUMERIC user-defined-function is in
    Sample UDFs for Migration
    Last edited by tonkuma; 02-06-11 at 10:23. Reason: Remove " , fam" from example 1).

Posting Permissions

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