Single-Row Functions

Single-Row Functions

1. Basis of defining

   • Input parameters list
     •  zero or more arguments  
   • Data type of its resultant value
   • encapsulates the details of the processing

2. Operating on Character Data

   • lower('SQL') = sql upper('sql') = SQL initcap('sql') = Sql
   • LENGTH(string)
     • length('A short string') = 14
   • CONCAT(string 1, string 2)
     • concat('SQL is',' easy to learn.') = SQL is easy to learn.
   • SUBSTR(string, start position, number of characters)
     •  substr('http://www.domain.com',12,6) = domain  
   • INSTR(source string, search item, [start position],[nth occurrence of search item])
     •  instr('http://www.domain.com','.',1,2) = 18  
   • LPAD(string, length after padding, padding string) RPAD(string, length after padding, padding string)
     •  rpad('#PASSWORD#',11,'#') = #PASSWORD## lpad('#PASSWORD#',11,'#') = ##PASSWORD#  
   • TRIM([trailing|leading|both] trimstring from s)
     • trim('#' from '#PASSWORD#') = PASSWORD
   • REPLACE(string, search item, replacement item)
     •  replace('#PASSWORD#','WORD','PORT') = #PASSPORT#  

3. Operating on Numeric Data

   • ROUND(number, decimal precision)
     •  round(42.39,1) = 42.4  
   • TRUNC(number, decimal precision)
     •  trunc(42.39,1) = 42.3  
   • MOD(dividend, divisor)

4. Operating on Date Information

   • MONTHS_BETWEEN(date1, date2)
     •  months_between('01-FEB-2008','01-JAN-2008') = 1  
   • ADD_MONTHS(date, number of months)
     •  add_months('01-JAN-2008',1) = 01-FEB-2008  
   • LAST_DAY(date)
     •  last_day('01-FEB-2008') = 29-FEB-2008  
   • NEXT_DAY(date, day of the week)
     •  next_day('01-FEB-2008','Friday') = 08-FEB-2008  
   • SYSDATE
     •  sysdate = 17-DEC-2007  
   • If the last two digits of the current year and specified year lie between 0 and 49, the current century is returned. Suppose the current year is 2014. The date assigned for 24-JUL-04 is 24-JUL-2004.
   • If the last two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned. Suppose the current year is 2014. The date assigned for 24-JUL-94 is 24-JUL-1994
   • If the last two digits of the current and specified years lie between 50 and 99, the current century is returned by default. If the current year is 2055, the date assigned for 24-JUL-94 is 24-JUL-2094.
   • If the last two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned. If the current year is 1975, the date assigned for 24-JUL-17 is 24-JUL-2017.
   • Date1 – Date2 = Num1 Date1 – Num1 = Date2 Date1 = Date2 + Num1 Date1 = Date2 – Num1
   • ROUND(source date, [date precision format])
   • TRUNC(source date, [date precision format])

5. Categories

   • Character
   • Numeric
   • Date
   • Conversion
     •  TO_CHAR, TO_NUMBER, and TO_DATE  
   • General
     •  They simplify working with NULL values and facilitate conditional logic within a SELECT statement. These include the NVL, NVL2, NULLIF, COALESCE, CASE, and DECODE functions  

6. Multiple-Row Functions

   •  As the name suggests, this category of functions operates on more than one row at a time. Typical uses of multiple-row functions include calculating the sum or average of the numeric column values or counting the total number of records in sets. These are sometimes known as aggregation or group functions