How to convert the first letter of each word to UPPERcase except stop words with PostgreSQL

Last week, I had to convert the first letter of each word to uppercase with PostgreSQL. PG has a very useful builtin function to do it: initcap(). However, it modifies every word of the string or column without flexibility. In my case, I had to convert all of them except some stop words, so initcap() was not enough.

Here is what I did. I’m sharing it because I’ve found a lot of very too long and complicated functions on google. I prefer to keep it simple.

It’s straightforward.

  1. Convert everything with the initcap() function
  2. Create an array of words you don’t want to be converted and for each element,  reconvert to lowercase the word with the value from the array with the replace() function.

You need to be careful to not replace part of a longer word, like “for” and “fortune“. In my example, the words were easy to identify because there were delimited by spaces. You can use a the regexp_replace() function to make sure that only the stop words are replaced if you don’t have obvious delimiters.

Oracle equivalent of MySQL group_concat

MySQL has a very useful function called GROUP_CONCAT() that concatenate the aggregated values of a group (GROUP BY). With Oracle 11G, you can use listagg to do the same, but Oracle 10G does not have an equivalent function.

If you are still working with Oracle 10G, this is the simplest way I found to concatenate aggregated values.

The separator used CANNOT be present in any of the concatenated values . It can be a problem if you deal with large text. I used “|” because I know it’s a character that cannot be present in my values.

Original Data:

Oracle Group Concat

 

 

 

 

 

Concatenated Data

Oracle Group Concat 2

 

 

 

References:

How to search for a specific column in all Oracle schema

As an ETL developer, this is something I have to do when developers of a source database application have to extend the length of a field. Because that field can be used in so many different projects and/or datamart, I need to find all the possible tables and views of all schemas having the field in my database.

Here is how I do it.. Suppose i’m looking for a column named “awesome_id”.  All I have to do is to execute this query with a user having the privileges to read into all schemas.

It will return all the tables having a column with the word “awesome_id”.

But it didn’t look the queries used to build views. Instead I have to use:

Unfortunately, it only returns the first 4000 characters of the queries, but the lookup is done on the complete queries.

Three different ways to create a CSV with MySQL

There are 2 well known ways to create a CSV using MySQL. But there is a technique I’ve been using for many years that most blogs and forums don’t talk about. I do believe it’s worth showing the technique I use since it’s faster.

First, there is the “standard” way:

The second technique is by using mysql client and sed

And the technique I use is by using the CSV storage engine.

I now have a file named test_csv_table.CSV in my MySQL datadir! If you don’t know where is your datadir, use the statement:

Obviously, it requires to have the privileges to read the mysql datadir. Also, keep in mind the CSV engine doesn’t support nullable columns.

What I like with that third technique is I don’t have to remember the complex syntax of the other techniques. It’s pretty straight forward.

Oracle SQL Update Statement with INNER JOIN

Oracle (the database) does not support joins in the UPDATE statement. This is one of many tiny annoying fact about Oracle. Here are 2 different ways to overcome that limitation.

The subqueries

 

The updatable view

 

MySQL provides, in my opinion, a  clearer syntax to update

 

I can now refer to my blog as an extension of my brain :)