Need help with reversing data in a column

I have a column called location and it has data in it like:

texas_dallas florida_miami

I am trying to create a SQL statement that will get rid of the _ and reverse the order so that I get:

dallas texas miami florida

So far I have this statement which seems to get rid of the underscore and gives me only the entry before the _.

SELECT SUBSTR(location, 1 , INSTR(location, ‘_’)-1 ) AS out开发者_如何学Pythonput from general;

I am having some trouble figuring out the rest of what I need to do.

EDIT Corrected order of output.

SQL> with general as (select 'texas_dallas' as location from dual
  2      union all select 'florida_miami' from dual)
  3  select substr(location, instr(location, '_') + 1)
  4      || ' ' || SUBSTR(location, 1 , INSTR(location, '_') -1) AS output
  5  from general;

OUTPUT
-------------------------------------------------------------------------------
dallas texas
miami florida

You need to add more to your expression. First concatenate a space, then use the SUBSTR function to extract the second word starting just after the underscore. Not supplying a length to SUBSTR takes the remainder of the text to the end.

select SUBSTR(location, 1 , INSTR(location, '_')-1 ) || ' ' || SUBSTR(location, INSTR(location, '_')+1 ) AS output 
from general ;

You can also trying a REGEXP_REPLACE function:

select regexp_replace(location,'^(\S+)\_(\S+)$','\2 \1') from general;