Regular Expression Check for Capital Names in PostgreSQL

2
votes

I have a database, which holds many first names, occurring like the following pattern:

The name can consist of many first names (like double, or triple names), separated by either a '-' or a ' '. Each of the names consists of either lowercase or UPPERCASE letters or a capital first letter and the rest lowercase.

I would like to write a query to count all names which have either just UPPERCASE letters, or do not have a capital letter after a break of two words.

Sample Table and Data

CREATE TABLE names( name VARCHAR, PRIMARY KEY(name) ); 
INSERT INTO names values('Veronika isabella'); 
INSERT INTO names values('Veronika Isabella'); 
INSERT INTO names values('Michael Karl Otto- Emil'); 
INSERT INTO names values('Michael karl-Otto-emil'); 
INSERT INTO names values('philipp'); 
INSERT INTO names values('Philipp');
1
sqlregexpostgresqlcount
if possible give sample table with data - Vivek S.
"Veronika isabella" - count++ "Veronika Isabella" - count=count "Michael Karl Otto Emil" - count=count "Michael karl-Otto-emil" - count++ "philipp" - count++ "Philipp" - count=count - Michael Werner
I meant create table script of your table and insert into table for test your case in my environment - Vivek S.
It's fine if this is just a programming exersise but please don't do this in the real world. Not everyone has "First" and "Last", or "Given" and "Family" names. They're not always written in the same order. They aren't always capitalised. Doing this in a real world app will make Joe McDonald, Alan de Silva (`"de Silva" is the last name), Prince, Olga D'Amico, and many others very angry. Don't do it. Names are weird and don't follow easily encoded rules. See kalzumeus.com/2010/06/17/… - Craig Ringer
Maybe as simple as where name <> initcap(name) - a_horse_with_no_name

1 Answers

1
votes
SELECT count(*) AS misfits
FROM   names
WHERE  name !~ '[[:lower:]]'   -- not a single lower case letter
OR     name ~ '\m[[:lower:]]'  -- lower case letter at beginning of a word
OR     name ~ '[[:lower:]][[:upper:]]'; -- lower case letter after upper case

Details in the manual.

Or maybe initcap() fits your requirements (like a_horse commented).

SELECT count(*) AS misfits
FROM   names
WHERE  name <> initcap(name);

SQL Fiddle.