Trim Function- Remove Leading and Trailing Blanks

Leading and trailing banks can be removed by Oracle trim function as shown below.

If we look at the lengths of the original and trimmed string (columns 4 and 5), we notice that the fourth record has 4 banks in the original string. However, the string is replaced with a NULL (length zero). If it is desirable that we want to keep one blank for the record, we can use NVL function to replace the NULL with a single blank.

In a project, I used the query similar to the following and fixed the data fields in debit card transaction.

nvl(substr( trim(SD_TERM_NAME_LOC),1,18),' ')

What I did was to first remove leading and trailing banks, then extract the first 18 characters (in case the terminal name is too long). In the case that the terminal name is all blanks or NULL, I replace them with a single blank. This data preparation step is necessary before we build a predictive model.