CODEWITHSUNDEEP
databaseoracle-database
yasmeen
yasmeen

Reputation: 55

Sorting alphanumeric character according to requirement

I have data in database as [AD AC AB AA H7 H2 H4 H3 H8 H6 H5 H9 H1 HL HJ HH HG HF 1S 1B 1G 1A] I need to display it as [AD AC AB AA HL HJ HH HG HF H9 H8 H6 H7 H5 H4 H3 H2 H1 1S 1G 1B 1A]. Can you please help me.

Till now i have tried this

select distinct Col1
from    Table1
order by regexp_substr(Col1  , '^[[:alpha:]]') , 
regexp_substr(Col1  , '[[:alpha:]]', 1,2) desc,
regexp_substr(Col1  , '^\d*'),
regexp_substr(regexp_substr(Col1  , '_[[:alpha:]]*') ,'[[:alpha:]]+') desc;

Thanks in advance

Upvotes: 0

Views: 60

Answers (2)

Ajay Venkata Raju
Ajay Venkata Raju

Reputation: 1158

Try this

select distinct col1  
from table_name
order by 
regexp_substr(Col1 , '^[[:alpha:]]') ,
SUBSTR(Col1, 2, 1) desc

Upvotes: 1

Arijit Kanrar
Arijit Kanrar

Reputation: 450

You need to provide more info or describe your requirement more for additional cases.

However from the data you have posted already, here is a query that can do it.

WITH mydat
     AS (SELECT 'AD' AS Col1 FROM DUAL
         UNION
         SELECT 'AC' AS Col1 FROM DUAL
         UNION
         SELECT 'AB' AS Col1 FROM DUAL
         UNION
         SELECT 'AA' AS Col1 FROM DUAL
         UNION
         SELECT 'H7' AS Col1 FROM DUAL
         UNION
         SELECT 'H2' AS Col1 FROM DUAL
         UNION
         SELECT 'H4' AS Col1 FROM DUAL
         UNION
         SELECT 'H3' AS Col1 FROM DUAL
         UNION
         SELECT 'H8' AS Col1 FROM DUAL
         UNION
         SELECT 'H6' AS Col1 FROM DUAL
         UNION
         SELECT 'H5' AS Col1 FROM DUAL
         UNION
         SELECT 'H9' AS Col1 FROM DUAL
         UNION
         SELECT 'H1' AS Col1 FROM DUAL
         UNION
         SELECT 'HL' AS Col1 FROM DUAL
         UNION
         SELECT 'HJ' AS Col1 FROM DUAL
         UNION
         SELECT 'HH' AS Col1 FROM DUAL
         UNION
         SELECT 'HG' AS Col1 FROM DUAL
         UNION
         SELECT 'HF' AS Col1 FROM DUAL
         UNION
         SELECT '1S' AS Col1 FROM DUAL
         UNION
         SELECT '1B' AS Col1 FROM DUAL
         UNION
         SELECT '1G' AS Col1 FROM DUAL
         UNION
         SELECT '1A' AS Col1 FROM DUAL)
  SELECT Col1,
         REGEXP_SUBSTR (Col1, '^[[:alpha:]]') first_char_else_null,
         REGEXP_SUBSTR (Col1, '^[[:digit:]]') first_num_else_null,
         SUBSTR (Col1, 2, 1) second_char,
         ASCII (SUBSTR (Col1, 2, 1)) ascii_second_char
    FROM mydat
ORDER BY REGEXP_SUBSTR (Col1, '^[[:alpha:]]') NULLS LAST,
         REGEXP_SUBSTR (Col1, '^[[:digit:]]') NULLS FIRST,
         ASCII (SUBSTR (Col1, 2, 1)) DESC;

Upvotes: 0

Related Questions