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
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.
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));
-- 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
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
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
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