Jan 04 2007
PL/SQL concatenating names
To concat first name, middle name, and last name in Pl/SQL:
first_name_field||’ ‘||middle_name_field||’ ‘||last_name_field
Â
To concat first name, middle initial, and last name do the following:
first_name_field||’ ‘||substr(middle_name_field,1,1)||’ ‘||last_name_field
Â
If you want a period(.) and space after the folks who do have a middle initial/name and if you want no space or period if they do not have a middle initial/name:
first_name_field||’ ‘||
decode(substr(middle_name_field,1,1),null,”,substr(middle_name_field,1,1)||’. ‘)||last_name_field
Â
I wrote that decode function all by myself without having to search on google or look at a pl/sql programming book. WOOT!
Â
Â
tags: concatenating names in pl/sql, oracle pl/sql concat, oracle pl/sql decode functions, pl/sql concat function, pl/sql decode function, pl/sql substr function, the power of decode functions
2 responses so far
Looks good! Once suggestion for a bit more efficiency you could leave out the substr in the first section of the decode. As so:
first_name||’ ‘||decode(middle_name_field,null,”,substr(middle_name_field,1,1)||’. ‘)||last_name_field
Thanks bork! Yes I can see how the extra substr is not necessary..