Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 954
  • Last Modified:

SQL Select Reformat Name

There's a table with a field or column that has a person's name in it.  It's in the format as follows:

lastname, firstname

Disregard that that's not normalized.  Have to put up with it.  Is there function in SQL that would me do a query on that field in the following format?:

firstname lastname

It is wthout the comma  Need a function embedded in the SQL select statement that formats the person's name in the latter format.  Using ORACLE.  Would prefer not to use any Oracle functions -- just standard SQL.
2 Solutions
slightwv (䄆 Netminder) Commented:
Try this:
select regexp_replace(name_column,'([^,]*)(,[ ]?)(.*)','\3 \1') from table_name;
>> Would prefer not to use any Oracle functions -- just standard SQL.
That makes it hard ... the number of 'standard' functions is limiting.  
The substring is considered as standard (as part of SQL99) ... in Oracle it is SUBSTR(), in SQL Server, MySQL, DB2 it is SUBSTRING(), MS Access it is MID() - so even though it is standard, it is syntactically different between different dbms implementations.
Whereas something like "locate in string" is not standard (I believe) ... however Oracle, MySQL, MS Access has INSTR(), SQL Server CHARINDEX() and LOCATE() in DB2.

So this
  ltrim(substr(name_col,instr(name_col,',')+1))||' '||substr(name_col,1,instr(name_col,',')-1)
is not 'standard' by my understanding of 'standard' ... but it should be portable to other dbms implementations, albeit with syntax changes.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now