[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1737
  • Last Modified:

PostgreSQL Custom Function to extract Name Parts - First, Last, Middle, and Suffix

In PostgeSQL, I want to create inline functions like I have in other RDBMS'.  

I want to be able to pass in a name field that has the format of: Last Name [space] optional Name Suffix, First Name[space] Middle Name and create 4 different inline functions that return each name part: 1. getFirstName(FullName), getMiddleName(FullName), getLastName(FullName), getNameSuffix(FullName)

The code would be something like this.  

CREATE FUNCTION getFirstName(character varying) RETURNS character varying AS
[code to extract out First Name] LANGUAGE sql;

The use of the function would look something like this:  

SELECT getFirstName('Smith Jr., Joseph F.' ) FROM emp;

Would return 'Joseph'

I have tried to created a function several times, but keep getting errors, please help with my syntax handicap!  I am used to creating inline user defined functions for SQL Server 2000, but am struggling with PostgreSQL syntax differences when it comes to function syntax.

Thanks,

FT
0
FreightTrain
Asked:
FreightTrain
  • 2
1 Solution
 
FreightTrainAuthor Commented:
Here is how I solved this...if someone has a better idea, I will keep this question open for a better or alternative way, as I am just learning PostgreSQL...

CREATE OR REPLACE FUNCTION pbs."getFirstName"("FullName" character varying)
  RETURNS character varying AS
$BODY$SELECT TRIM(split_part(split_part($1, ', ', 2), ' ', 1))$BODY$
  LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
0
 
earth man2Commented:
testdb=> select * from x;
 id |            fullname            
----+---------------------------------
  1 | O'Brian, Brian Bernard Bertrand
(1 row)

testdb=> select surname,
btrim( split_part( fore_names, ' ', 1 ) ) as forename,
substr( fore_names, strpos( fore_names,' '), length( fore_names )) as middle_names
from
( select btrim( split_part( fullname, ',', 1 ) ) as surname,
  btrim(substr( fullname, strpos( fullname, ',' )+1, length( fullname ))) as fore_names from x) as foo;
 
surname | forename |   middle_names    
---------+----------+-------------------
 O'Brian | Brian    |  Bernard Bertrand
(1 row)
0
 
earth man2Commented:
testdb=> select * from x;
 id |            fullname            
----+---------------------------------
  1 | O'Brian, Brian Bernard Bertrand
  2 | O'Brian, Barnaby
(2 rows)

testdb=> select surname, btrim( split_part( fore_names, ' ', 1 ) ) as forename,
case when strpos( fore_names,' ') = 0 then null
else substr( fore_names, strpos( fore_names,' ') , length( fore_names )) end as middle_names
from (
select btrim( split_part( fullname, ',', 1 ) ) as surname,
btrim(substr( fullname, strpos( fullname, ',' )+1, length( fullname ))) as fore_names from x ) as foo;

 surname | forename |   middle_names    
---------+----------+-------------------
 O'Brian | Brian    |  Bernard Bertrand
 O'Brian | Barnaby  |
(2 rows)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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