Oracle query to reverse order of First and Last name

I am looking for an Oracle Sql query for following
   I have a table for users and username filed has values  LastName, FirstName (  like Gates, Bill )
   I need to fetch the value as FirstName Name  ( Like Bill Gates)
Sam OZAsked:
Who is Participating?
select trim(regexp_substr(username,'[^,]+',1,2)) || ' ' || trim(regexp_substr(username,'[^,]+',1,1)) from yourtable
Geert GOracle dbaCommented:
i'd use 2 fields (or more) to store that data in ...
Some people have more than 2 names in their name

And if that's stored with multiple ',' ... then even sdstuber's query will not show everything
Mark GeerlingsDatabase AdministratorCommented:
If you prefer to do this with a query that may be easier to understand (but may be not quite as efficient as sdstuber's) you could use a combination of the Oracle "substr" (sub-string ) and "instr" (in-string) operators like this:

select trim(substr(username,instr(username,',') +1),' ') ||' '|| substr(username,1, instr(username,',') -1)
from [your_table]
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.