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

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:
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.

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...
2) If you want position all numeric strings after alphabet strings or mixed strings...
Where ISNUMERIC user-defined-function is in
Sample UDFs for Migration