Of Camels and People: Converting back and forth from Camel Case, Pascal Case to underscore lower case

When it comes to naming things in databases and languages, there are various common standards. For many languages the camel family of namings is very popular. For unix based databases usually UPPER or lower _ is the choice and for databases such as SQL Server and MySQL which allow you to name your columns with mixed casing but couldn't care less what case you express them in selects, you get a mish mush of styles depending on what camp the database user originated from.

So to summarize the key styles and the family of people

• camelCase : lastName - employed by SmallTalk, Java, Flex, C++ and various C derivative languages.
• Pascal Case: (a variant of Camel Case) -- LastName which is employed by C#, VB.NET, Pascal (and Delphi), and SQL Server (and some MySQL windows converts). Also often used for class names by languages that use standard camelCase for function names.
• lower case _ last_name : often found in C, a favorite among PostgreSQL database users. (some MySQL)
• upper case _ LAST_NAME : a favorite among Oracle Users (some MySQL Oracle defectors)

Being at the cross roads of all the above, we often have to deal with the various above as well as having internal schizophrenic strife and external fights. The internal turmoil is the worst and is worse than an ambidextrous person trying to figure out which hand to use in battle. For these exercises, we'll demonstrate one way how to convert between the various conventions. These are the first thoughts that came to our mind, so may not be the most elegant.

Converting from PostgreSQL common field names to camel case and others

For these exercises, lets pretend we have a table in PostgreSQL called people

CREATE TABLE people(person_id serial PRIMARY KEY, 
    first_name varchar(75), last_name varchar(75)); 

Question: How do you convert from lower case _ to Pascal case?

Answer:

-- pascal case
SELECT column_name, replace(initcap(replace(column_name, '_', ' ')), ' ', '') As pascal_case
FROM information_schema.columns
WHERE table_name = 'people';



column_name | pascal_case
------------+-------------
person_id   | PersonId
first_name  | FirstName
last_name   | LastName

Question: How do you convert from lower case _ to camel case?

Answer:

SELECT column_name, lower(substring(pascal_case,1,1)) || substring(pascal_case,2) As camel_case
FROM (
SELECT column_name, replace(initcap(replace(column_name, '_', ' ')), ' ', '') As pascal_case
FROM information_schema.columns
WHERE table_name = 'people' ) As foo;


column_name | camel_case
------------+------------
person_id   | personId
first_name  | firstName
last_name   | lastName

Converting from PostgreSQL lower _ to Oracle upper _

Of course this one is trivial, but we include for completeness.

Answer:

-- PostgreSQL to Oracle
SELECT column_name, upper(column_name) As oracle_name
FROM information_schema.columns
WHERE table_name = 'people';



 column_name | oracle_name
-------------+-------------
 person_id   | PERSON_ID
 first_name  | FIRST_NAME
 last_name   | LAST_NAME

Converting from camel case and pascal case to lowercase underscore

Of course you often have the problem of some camel lover creating table columns in your database. You can fix this with SQL DDL hacks and PostgreSQL regular expressions.

 CREATE TABLE "People"("personId" serial PRIMARY KEY, 
    "firstName" varchar(75), "lastName" varchar(75));

For converting the other way, using regular expressions (thank goodness PostgreSQL has this feature) seems to be the shortest way we can think to do this.

-- camel case to lower underscore

-- take all capital letters A-Z (we wrap a () so we can backreference
--  replace each capital with _ and original capital, g for greedy replace
-- then lower case it all
SELECT column_name, 
    lower(regexp_replace(column_name, E'([A-Z])', E'\_\\1','g')) As regular_pgstyle
FROM information_schema.columns
WHERE table_name = 'People';


 column_name | regular_pgstyle
-------------+-----------------
 personId    | person_id
 firstName   | first_name
 lastName    | last_name