5 Replies Latest reply: Oct 3, 2013 2:39 AM by Sardar Nale RSS

    oracle query sort by case sensitive

    Sardar Nale

      Hi All,

      I am using oracle database 11g.

      My use case is I do have a table with following values

      Table name -test

      product id     productsortdescription
      H58098        ACETAMIDOHYDROXYPHENYLTHIAZOLE
      043994         Alloy .MM.INTHICK
      

       

       

      My query is

       

      select * from test order by productsortdescription;
      

       

       

      this query gives result as is like

      product id     productsortdescription

      H58098        ACETA

      product id     productsortdescription
      H58098        ACETAMIDOHYDROXYPHENYLTHIAZOLE
      043994         Alloy .MM.INTHICK
      

      MIDOHYDROXYPHENYLTHIAZOLE

      043994         Alloy .MM.INTHICK

      but Expected output/result should be like below:

       

      product id     productsortdescription

      043994     Alloy .MM.INTHICK

      H58098      ACETAMIDOHYDROXYPHENYLTHIAZOLE

       

      as All and ACE in productsortdescription

      l is in small case than C.

       

      NLS Session parameters are as following

      SELECT * from NLS_SESSION_PARAMETERS;

       

      NLS_LANGUAGE    AMERICAN

      NLS_TERRITORY    AMERICA

      NLS_CURRENCY    $

      NLS_ISO_CURRENCY    AMERICA

      NLS_NUMERIC_CHARACTERS    .,

      NLS_CALENDAR    GREGORIAN

      NLS_DATE_FORMAT    DD-MON-RR

      NLS_DATE_LANGUAGE    AMERICAN

      NLS_SORT    BINARY

      NLS_TIME_FORMAT    HH.MI.SSXFF AM

      NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM

      NLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZR

      NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR

      NLS_DUAL_CURRENCY    $

      NLS_COMP    BINARY

      NLS_LENGTH_SEMANTICS    BYTE

      NLS_NCHAR_CONV_EXCP    FALSE

       

       

       

       

      Please help me out in this scenario.

        • 1. Re: oracle query sort by case sensitive
          riedelme

          There are ways to do this by setting initialization parameters or (easier) using upper() and lower() on the sort columns in the SQL.  Use your favorite search engine to find articles on case independent sorts in Oracle.  Case independent searches may or may not affect performance.


          • 2. Re: oracle query sort by case sensitive
            Etbin

            Are you looking for something like

             

            with

            names as

            (select 'Etbin' n from dual union all

            select 'etbin' from dual union all

            select 'ETBIN' from dual union all

            select 'ACETAMIDOHYDROXYPHENYLTHIAZOLE' from dual union all

            select 'Alloy .MM.INTHICK ' from dual union all

            select 'another name' from dual union all

            select 'Aalto; Hugo Alvar Henrik' from dual union all

            select 'astrerisk' from dual union all

            select 'Ezekiel' from dual union all

            select 'zylog' from dual

            ),

            expansion as

            (select n,listagg(duo) within group (order by l) ord

               from (select n,level l,

                            case when ascii(substr(n,level,1)) between 97 and 122

                                 then '0'

                                 when ascii(substr(n,level,1)) between 65 and 90

                                 then '1'

                                 else '2'

                            end || substr(n,level,1) duo

                       from names

                     connect by prior n = n

                            and prior dbms_random.random is not null

                            and level <= length(n)

                    )

              group by n

            )

            select n

              from expansion

            order by ord

             

            N
            another name
            astrerisk
            etbin
            zylog
            Aalto; Hugo Alvar Henrik
            Alloy .MM.INTHICK
            ACETAMIDOHYDROXYPHENYLTHIAZOLE
            Etbin
            Ezekiel
            ETBIN

             

            Regards

             

            Etbin

            • 3. Re: oracle query sort by case sensitive
              jihuyao

              Simply switch the case of each letter for order by may be what you want

               

               

                1  with t as (

                2  select 'H58098' id,        'ACETAMIDOHYDROXYPHENYLTHIAZOLE' str from dual

                3  union all

                4  select '043994' id,         'Alloy .MM.INTHICK' str from dual

                5  )

                6  select id, str,

                7  translate(str,

                8  'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

                9  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') str2

              10  from t

              11* order by str2

              SQL> /

               

              ID     STR                            STR2

              ------ ------------------------------ ---------------------------------------------------------------

              043994 Alloy .MM.INTHICK              aLLOY .mm.inthick

              H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE acetamidohydroxyphenylthiazole

               

              • 4. Re: oracle query sort by case sensitive
                BobLilly

                One option is to use the NLSSORT function. Most ASCII character sets sort uppercase before lowercase, but EBCDIC sorts lowercase before uppercase. So you can use

                 

                with t as (

                select 'H58098' id, 'ACETAMIDOHYDROXYPHENYLTHIAZOLE' str from dual union all

                select '043994' id, 'Alloy .MM.INTHICK' str from dual

                )

                select id, str

                from t

                order by NLSSORT(str,'NLS_SORT=EBCDIC')
                ;

                ID     STR                          

                ------ ------------------------------

                043994 Alloy .MM.INTHICK            

                H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE

                 

                Of course, if your strings can contain non-alpha characters you'll have to verify that the EBCDIC sort order is acceptable for them as well. To check it you can use something like

                 

                with t (str, ascii) as (

                select chr(level+32), level+32 from dual connect by level <= 126-32

                )

                select str, ascii from t order by NLSSORT(str,'NLS_SORT=EBCDIC')

                ;

                 

                or just do an internet search on EBCDIC. You can also substitute other linguistic sorts for EBCDIC and see if any of them satisfy your needs. See Appendix A in the Globalization Support Guide for the list of valid NLS_SORT settings.

                 

                You say you're on 11g--if you mean 11.2.x, then you can use the listagg function to get a more compact view of the sort order:

                 

                with t (str, ascii) as (

                select chr(level+32), level+32 from dual connect by level <= 126-32

                )

                select listagg(str) within group (order by NLSSORT(str,'NLS_SORT=EBCDIC')) as EBCDIC_order

                from t

                ;

                EBCDIC_order
                ----------------------------------------------------------------------------------------------

                .<(+|&!$*);-/,%_>?`:#@'="abcdefghijklmnopqr~stuvwxyz[^]{ABCDEFGHI}JKLMNOPQR\STUVWXYZ0123456789

                 

                Regards,

                Bob

                • 5. Re: oracle query sort by case sensitive
                  Sardar Nale

                  Thank you all for your help.

                   

                  I tried answer by BobLilly

                   

                  order by NLSSORT(str,'NLS_SORT=EBCDIC').


                  Its working correctly .

                  Thank you very much.