Search code examples
sqlpostgresqlformatting

SQL Formatting a string (name)

I can't quite figure this out.

I have a name_matrix table that unfortunately comes to me in one column as shown below. Some of the names come in with a space after the comma, some of them without it. Some of the names come formatted correctly, while some are all lowercase, or all uppercase.

rowid name
1 Smith,John
2 Smith, John
4 smith,john
5 SMITH, JOHN

I'm trying to figure out how to remove the space, if there is one... separate the last name and the firstname... capitalize the first letter of each and lowercase the rest of the name.

So far I have this query, that formats the name correctly IF there is no space. This query correct rows 1 and 4 -

SELECT Upper((LEFT(split_part(name, ',',2),1))) ||
         lower(substring(split_part(name, ',',2), 2, 99)) AS firstname, 
       Upper((LEFT(split_part(name, ',',1),1))) ||
         lower(substring(split_part(name, ',',1), 2, 99)) AS lastname
FROM name_matrix`

If there is a space, the result looks like this; (I added an underscore to show a space).

firstname lastname
_john Smith

I tried to use a REPLACE in front of the UPPER, but that only removed the space and didn't capitalize the firstname first char.

Solution

  • You can use a combination of initcap (for capitalizing first letter even if multiple first letters such as Mary Lee Smith), ltrim (for removing the leading space) and split_part. Assuming you have names such as MARY LEE SMITH, you want to be careful or avoid using TRIM or REPLACE when dealing with your spaces.

    select 
 rowid, 
 name, 
 initcap(split_part(name, ',', 1)) as last_name, 
 initcap(ltrim(split_part(name, ',', 2))) as first_name --ltrim removes space by default
from 
 name_matrix
    rowid name last_name first_name
    1 Smith,John Smith John
    2 Smith, John Smith John
    4 smith,john Smith John
    5 SMITH, JOHN Smith John

    fiddle