Inbuilt Number functions, I didn’t know that

Character based functions were discussed in the previous post. We’ll go ahead and look into the number function now. Some of my interesting observations are here –

• round(number, [decimal precision]) – This function rounds off a number to the lowest or highest value depending upon the decimal precision. The decimal precision is optional which means rounding to the nearest whole number. What happens when the decimal precision is negative. In such cases, the rounding off happens from the left of the decimal point. So, if it’s -1, then the rounding off happens at the first digit on the left side to the decimal point. It also happens to be significant digit.Lets look at something interesting –
  
   

  round(number, decimal precision)	Output
  round(32.54, 1)	32.5
  round(32.55, 1)	32.6
  round(32.55, 0)	33
  round(32.45, 0)	32
  round(32.45, -1)	30
  round(32.45, -2)	0
  round(52.45, -2)	100

  Point to remember – If the decimal precision is negative, say -n, the significant digit will be the nth digit left to the decimal point. If the significant digit is less than 5, round the source number to the nearest multiple of 10^n less than source number. If the significant digit is 5 or more, round the source number to the nearest multiple of 10^n greater than source number.

  So, in case of round(32.45, -1), n = 1 which means the significant digit is 2, which is less than 5 and therefore we’ll find the nearest multiple of 10 less than 32.45 which equals 30. Let’s take one more example – round(52.45, -2). Here n = 2 which makes 5 as the significant digit and therefore we’ll round it to the nearest multiple of 100 greater than 52.45, which equals 100.

• trunc(number, [decimal precision]) – It performs truncate operation on the numeric value on the decimal precision specified. in numeric truncation, the resulting value drops the numbers at the decimal precision and does not attempt to round-down or round-up if the decimal precision is positive.
  If the decimal precision is negative (say -n), then the source number is zeroed down from the nth digit.
  
   

  trunc(number, decimal precision)	Output
  trunc(1666.54, -3)	1000
  trunc(1666.66, -2)	1600
  trunc(1601.916718)	1601
  trunc(2601.958,1)	2601.9
  trunc(2601.958,2)	2601.95

Inbuilt Character functions, I didn’t know that

I was going through the usual PL/SQL in-built functions and thought of skipping the entire chapter. Then I realized that this lazy Sunday afternoon could be used to test these functions out. Something I had never done. And believe me, I learnt a lot of new things about them today. Some of them (mainly character functions) are here –

• initcap(input string) – This function converts a string of characters into capitalized case. The first letters os each word are converted to their upper case equivalents, while the remaining characters are converted to their lowercase equivalents. Now a word can be separated by any one of the following – blank space ( ), underscore (_), percentage (%), punctuations marks and special characters.

  select initcap('init cap OR init_cap OR init%cap OR init,cap Or init#cap') OUTPUT from dual
  OUTPUT
  --------------------------------------------
  Init Cap Or Init_Cap Or Init%Cap Or Init,Cap Or Init,Cap
  

• substr(source, start position, number of characters) – This function accepts 3 parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified starting point. What if the start position is negative? When its negative, it loops back from the end and starts extracting from there. The negative index could be used if you need to extract characters from the end. Suppose, you need the last 4 characters of the employees last name –

  select substr(EMP_LAST_NAME, -4) from dual
  

• instr(source, search item, [start position], [nth occurrence]) – This function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins. It returns zero when it does not find the search item. So, its always advisable to use the start position as 1 and not 0 because if the search item is found in the beginning, it’ll return 0 (which could also mean that the search item was not found).

  select instr('my my my my', 'my', 0, 1) from dual;
  INSTR('MYMYMYMY','MY',0,1)
  --------------------------
  0
  

  select instr('my my my my', 'myd', 0, 1) from dual
  INSTR('MYMYMYMY','MYD',0,1)
  ---------------------------
  0
  

• lpad(string, length after padding, pad string) OR rpad(string, length after padding, pad string) – This function adds a padding string of characters to the left or right to the string until it reaches the specified length after padding. Now the interesting part. What happens if the input string’s length is 6, the length after padding is 7 and the length of the padding string is 2. In this case, the first letter of the padding string is padded. Let’s see –

  select lpad('VIJAY', 6, 'XYZ') from dual;
  LPAD('
  ------
  XVIJAY
  

  Additionally, if the target length is less than the length of the input, the input string is truncated and returned.

  SQL> select rpad(SYSDATE,4,'$#') from dual;
  RPAD
  ----
  02-A
  

• {r,l}trim – This function, as the name suggests, trims characters, wither from left, or right, or both. This function needs an entire post.

Trim me please

My hair growth is very very slow. I trimmed my hair two months back and still, I see no substantial hair growth. Above all, they all are standing straight, as if I just had an electric shock. One thing I’ve learnt – No more trimming. Oracle has a more useful trimming function with various flavors.

Let’s start with the basic one – trim(input). This function will trim blank spaces from both sides, left and right. If you need to trim blank spaces only on the left side, use ltrim(input). And yeah, rtrim(input) to trim blank spaces from the right.

What if you want to trim a particular character. For that, we’ll use the left trim and right trim flavors – ltrim(input, trim character) and rtrim(input, trim character). We do not have a trim function which takes two parameters.

select rtrim('VIJAYY', 'Y') OUTPUT from dual;

OUTPUT
-------
VIJA

The two variations of ltrim and rtrim are the trim(leading trimchar from input) and trim(trailing trimchar from input) respectively. The trimchar can only contain one character. If the trimchar contains more than one character, we get the ORA-30001: trim set should only one character error.

select trim(trailing 'Y' from 'VIJAYY') OUTPUT from dual;

OUTPUT
-------
VIJA

select trim(leading 'V' from 'VVIJAY') OUTPUT from dual;

OUTPUT
-------
IJAY

We also have a trim flavor that trims a single character from both sides – trim(both trimchar from input).

select trim(both 'M' from 'MALAYALAM') OUTPUT from dual;

OUTPUT
--------
ALAYALA

If we forget the leading, trailing or both keyword, ‘both’ acts as the default one. The basic trim (first one to be discussed) is same as trim(both ‘ ‘ from input).