CODEWITHSUNDEEP
sqloracle-databasesortingoracle11g
sTg
sTg

Reputation: 4424

Oracle : Sort a list on second word in SQL Query?

In Oracle, how do i sort a value for eg: Name in SQL using its second word. Looks a bit tricky to me. For eg: If in a table i have the names as below:

I have a list of objects which i want to sort on basis of name. I have done coding where it does get sorted on basis of name but i have a slight different requirement.

The names are for Example:

Bull AMP
Cat DEF
Dog AMP
Frog STR
Zebra DEF

I want the list to be :

Bull AMP
Dog AMP
Cat DEF
Zebra DEF
Frog STR

To be sorted by second word.

I tried the below query but it didnt seem to work.

SELECT NAME, 
  SUBSTR(NAME, INSTR(' ',NAME), 
  LENGTH(NAME) - INSTR(' ',NAME) +2) AS word2
FROM animal_master
ORDER BY SUBSTR(NAME,INSTR(' ',NAME), LENGTH(NAME) - INSTR(' ',NAME) +2) asc;

Can anyone please guide whats wrong.

Upvotes: 2

Views: 437

Answers (4)

Gary_W
Gary_W

Reputation: 10360

Clean up all that nested instr/substr stuff with regexp_replace:

order by regexp_replace(NAME, '.* (\w)', '\1'), NAME;

The regex matches and remembers the last "word" after the space, then orders by that first.

Upvotes: 1

balaiah
balaiah

Reputation: 24

select display_name from employee order by substr(upper(display_name),instr(upper(display_name),' ',1));

Upvotes: 0

Damith
Damith

Reputation: 437

Try the following.

SELECT NAME
FROM animal_master
ORDER BY TRIM(SUBSTR(NAME, (INSTR(NAME, ' ', 1, 1) + 1), (INSTR(NAME, ' ', 1, 2) - INSTR(NAME, ' ', 1, 1) - 1))) asc;

Below query explains better..

SELECT NAME
    FROM (
        SELECT 
            NAME,
            (INSTR(NAME, ' ', 1, 1) + 1) first_occurrence_of_space,
            (INSTR(NAME, ' ', 1, 2) - INSTR(NAME, ' ', 1, 1) - 1) second_occurrence_of_space
    FROM animal_master)
ORDER BY TRIM(SUBSTR(NAME, first_occurrence_of_space, second_occurrence_of_space)) asc;

Upvotes: 0

Ucello
Ucello

Reputation: 276

Your INSTR function had back to front arguments, so that is why you were gettting the incorrect results. I would recommend using the following:

SELECT NAME
FROM animal_master
ORDER BY SUBSTR(NAME,INSTR(NAME, ' ')) asc;

The SUBSTR(NAME,INSTR(NAME, ' ')) returns only the second word, and you order by this second word. If you also want to order by the first, then the second word you can do something like this:

ORDER BY SUBSTR(NAME,INSTR(NAME, ' ')), NAME

Upvotes: 2

Related Questions