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

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?

-- 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?

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.

-- 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