CODEWITHSUNDEEP
sqloracle-database
Christopher
Christopher

Reputation: 691

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 output from general;

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

Upvotes: 2

Views: 82

Answers (3)

Aitor
Aitor

Reputation: 3429

You can also trying a REGEXP_REPLACE function:

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

Upvotes: 0

Shannon Severance
Shannon Severance

Reputation: 18410

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

Upvotes: 4

Morbo
Morbo

Reputation: 536

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 ;

Upvotes: 1

Related Questions