{"id":747,"date":"2014-10-20T07:01:40","date_gmt":"2014-10-20T14:01:40","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=747"},"modified":"2014-10-14T14:37:29","modified_gmt":"2014-10-14T21:37:29","slug":"convert-camelcase-to-underscores-using-sed-and-tr-greenplum-mpp-refactor","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2014\/10\/convert-camelcase-to-underscores-using-sed-and-tr-greenplum-mpp-refactor\/","title":{"rendered":"Convert CamelCase to Underscores using SED and tr: GreenPlum MPP Refactor"},"content":{"rendered":"

All of our SQL Server databases use CamelCase as the naming convention. Unfortunately, GreenPlum MPP does not support CamelCase (without the use of quotes that is). Since I was transitioning a table from SQL Server to GreenPlum MPP and because I'm becoming a bash wizard (ha!) I wondered if there was a simple way to convert camel case column names to underscores. It took a bit of trial and error and some expert level googling but the following did the trick. (Feel free to leave a comment if there is a more efficient way to do this).<\/p>\n

From the directory where you have your file run the following command. It will identify upper case characters in the file called phil_camel.txt and insert an underscore before them. The updated file will be called phil_underscore.txt (the original file will not be modified).<\/p>\n

sed -r 's\/([a-z]+)([A-Z][a-z]+)\/\\1_\\l\\2\/g' phil_camel.txt>phil_underscore.txt<\/pre>\n
If you examine the file you'll see that the underscores have been added but the uppercase characters have been preserved. Generally when you use underscores to separate words in a database you use only lowercase letters. The following command will replace all upper case letters in phil_underscore.txt with lowercase letters and create a new file called phil_under_lower.txt.<\/p>\n
tr '[:upper:]' '[:lower:]' <phil_underscore.txt>\u00a0phil_under_lower.txt<\/pre>\n
That's it. You're now a bash genius like me.<\/p>\n
\"Why<\/a>
Why is this picture here? Because it showed up when I googled \"bash\".<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
All of our SQL Server databases use CamelCase as the naming convention. Unfortunately, GreenPlum MPP does not support CamelCase (without the use of quotes that is). Since I was transitioning a table from SQL Server to GreenPlum MPP and because I'm becoming a bash wizard (ha!) I wondered if there was a simple way to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[147],"tags":[160,180,161,159],"class_list":["post-747","post","type-post","status-publish","format-standard","hentry","category-greenplum","tag-bash","tag-greenplum","tag-linux","tag-mpp"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/747","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=747"}],"version-history":[{"count":1,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/747\/revisions"}],"predecessor-version":[{"id":749,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/747\/revisions\/749"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=747"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=747"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=747"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}