Requirement My requirement is to check if the name of a person contains Initials , replace it with Caps of that letter and period. For my requirement initials will always be single letters names , or single letter strings in the name before of after a space.
Challenge I am using REGEXP_REPLACE and use \y to search for a single character within word boundary. This is working fine but now i need to use that same character in my replace string and make it Caps and add a period. Basically the $1 in below query should be replaced with the string i get from the pattern \y[(A-Z)(a-z)]\y
select REGEXP_REPLACE(first_name,'\y[(A-Z)(a-z)]\y',upper($1|| '.'),'g' )from public.regextest
Sample data
Below is table of sample data and code to create sample data
first_name Suggested value
A A.
H M H. M.
Lars E Lars E.
Jo Jo
BO Bo
X X X X. X. X.
M HAMED M. HAMED
M P Rajan M. P. Rajan
Rajan M P Rajan M. P.
Below is code to create the sample data .
create table public.regextest( first_name varchar(100))
insert into public.regextest values ( 'A')
,('H M')
,('Lars E')
,('Jo')
,('BO')
,('X X X')
,('M HAMED')
,('M P Rajan')
,('Rajan M P')
Do the replacement, then use initcap to capitalize the first letter of each word.
select
initcap(
regexp_replace(first_name, '\y([[:alpha:]])\y(?!\.)', '\1.','g')
)
from public.regextest
The character class [[:alpha:]] matches all alphabetical characters, including accented ones. Flanking it with \y ensures we match only one. (?!\.) is a negative lookahead checking that it is not followed by a . without consuming that . in the match, that avoids doubling up. It's surrounded by () to capture it.
Then the replacement is the captured character, Postgres uses \1, with a dot.
Feed the result into initcap to fix the capitalization.