CODEWITHSUNDEEP
sqloracle-database
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

Oracle sorting data contains text & number

I'm using Oracle database and now I would like to sort my data that contains text and number.

Script for below data:

SELECT * FROM WA_GA_TBL_LINES
  WHERE LINENAME LIKE 'LEGO%' AND
        SECTIONID_FK = 'SC0013' AND
        LINENAME != 'LEGO 16'
  ORDER BY LPAD(LINENAME,
           (SELECT MAX(LENGTH(LINENAME))
              FROM WA_GA_TBL_LINES)) ASC

Data

LEGO 1
LEGO 2
LEGO 3
LEGO 4
LEGO 5
LEGO 6
LEGO 7
LEGO 8
LEGO 9
LEGO 10
LEGO 11
LEGO 15
LEGO 12A
LEGO 12B
LEGO 13A
LEGO 13B
LEGO 14A
LEGO 14B

As you can see LEGO 15 is not in the correct position.

What I want is LEGO 15 is on the last position (due to 15 is most high number)

LEGO 1
LEGO 2
LEGO 3
LEGO 4
LEGO 5
LEGO 6
LEGO 7
LEGO 8
LEGO 9
LEGO 10
LEGO 11
LEGO 12A
LEGO 12B
LEGO 13A
LEGO 13B
LEGO 14A
LEGO 14B
LEGO 15

Upvotes: 2

Views: 92

Answers (4)

Littlefoot
Littlefoot

Reputation: 142720

First of all, I'm not criticizing anyone; this is just another view, viewed over the first cup of coffee today. I also hope that it won't be nonsense.

Note that answers posted so far presume that the first word is always "LEGO". Well, based on sample data, it really is. Only the @OP knows the truth so I'm also only guessing, just like you, guys.

However, if that's so, I suspect that data model might not be properly normalized (i.e. LEGO should be in its own column).

If that "first word" changes, consider a slight modification in the ORDER BY clause. For example (based on @Saravana's code; thank you for the sample data!):

SQL> with legs(leg) as (
  2  select 'LEGO 1' from dual union
  3  select 'LEGO 2' from dual union
  4  select 'LEGO 3' from dual union
  5  select 'LEGO 4' from dual union
  6  select 'LEGO 5' from dual union
  7  select 'LEGO 6' from dual union
  8  select 'LEGO 7' from dual union
  9  select 'LEGO 8' from dual union
 10  select 'LEGO 9' from dual union
 11  select 'TRIAGO 10' from dual union   --> here
 12  select 'LEGO 11' from dual union
 13  select 'LEGO 15' from dual union
 14  select 'LEGO 12A' from dual union
 15  select 'VIGO 12B' from dual union    --> here
 16  select 'LEGO 13A' from dual union
 17  select 'LEGO 13B' from dual union
 18  select 'LEGO 14A' from dual union
 19  select 'LEGO 14B' from dual
 20  )
 21  select leg from legs
 22  order by regexp_substr(leg, '^\w+'),
 23           to_number(regexp_substr(leg, '\d+')),
 24           regexp_substr(leg, '\w+$');

LEG
---------
LEGO 1
LEGO 2
LEGO 3
LEGO 4
LEGO 5
LEGO 6
LEGO 7
LEGO 8
LEGO 9
LEGO 11
LEGO 12A
LEGO 13A
LEGO 13B
LEGO 14A
LEGO 14B
LEGO 15
TRIAGO 10   --> here
VIGO 12B    --> here

18 rows selected.

SQL>

Upvotes: 2

Mohd Ahmad
Mohd Ahmad

Reputation: 55

I have run query with this set of data and it is working fine.

select * from A order by to_number(regexp_substr(regexp_substr(A,'[^ ]+',1,2),'[^A-Z]+',1,1)) Asc;

Upvotes: 0

Saravana
Saravana

Reputation: 12817

You need to substring the number and alphabet from the LINENAME and do sorting

SELECT * FROM WA_GA_TBL_LINES 
WHERE LINENAME LIKE 'LEGO%' 
    AND SECTIONID_FK = 'SC0013' 
    AND LINENAME != 'LEGO 16' 
ORDER BY TO_NUMBER(SUBSTR(LINENAME,5, 3)),SUBSTR(LINENAME,8, 2)

you can control the order based on the alphabet when its null and not null cases

ORDER BY TO_NUMBER(SUBSTR(LINENAME,5, 3)) ASC ,SUBSTR(LINENAME,8, 2) ASC NULLS FIRST

SQL Fiddle

with legs(leg) as (
select 'LEGO 1' from dual union
select 'LEGO 2' from dual union
select 'LEGO 3' from dual union
select 'LEGO 4' from dual union
select 'LEGO 5' from dual union
select 'LEGO 6' from dual union
select 'LEGO 7' from dual union
select 'LEGO 8' from dual union
select 'LEGO 9' from dual union
select 'LEGO 10' from dual union
select 'LEGO 11' from dual union
select 'LEGO 15' from dual union
select 'LEGO 12A' from dual union
select 'LEGO 12B' from dual union
select 'LEGO 13A' from dual union
select 'LEGO 13B' from dual union
select 'LEGO 14A' from dual union
select 'LEGO 14B' from dual
) select leg from legs order by to_number(substr(leg,5, 3)),substr(leg,8, 2)

Results:

    |      LEG |
    |----------|
    |   LEGO 1 |
    |   LEGO 2 |
    |   LEGO 3 |
    |   LEGO 4 |
    |   LEGO 5 |
    |   LEGO 6 |
    |   LEGO 7 |
    |   LEGO 8 |
    |   LEGO 9 |
    |  LEGO 10 |
    |  LEGO 11 |
    | LEGO 12A |
    | LEGO 12B |
    | LEGO 13A |
    | LEGO 13B |
    | LEGO 14A |
    | LEGO 14B |
    |  LEGO 15 |

Upvotes: 3

shrek
shrek

Reputation: 887

The result can be achieved using REGEXP as follows - 

SELECT * FROM WA_GA_TBL_LINES 
WHERE LINENAME LIKE 'LEGO%' 
    AND SECTIONID_FK = 'SC0013' 
    AND LINENAME != 'LEGO 16' 
ORDER BY TO_NUMBER(REGEXP_REPLACE(LINENAME,'[^0-9]',''));

Result:

LEGO 1
LEGO 2
LEGO 3
LEGO 4
LEGO 5
LEGO 6
LEGO 7
LEGO 8
LEGO 9
LEGO 10
LEGO 11
LEGO 12A
LEGO 12B
LEGO 13A
LEGO 13B
LEGO 14A
LEGO 14B
LEGO 15

Upvotes: 4

Related Questions