ANSI SQL Extensions  «Prev 

Oracle built-in functions for SQL

One of the beneficial features about Oracle’s SQL is their great use of built-in functions.
If we look at all of the nice extensions to SQL such as
  1. DECODE and
  2. string translation functions,
it should be apparent that BIFs are an integral part of the Oracle SQL environment. In fact, there are so many BIFs that entire application programs can be written in vanilla, chocolate, rasberry, and butter pecan SQL.
In short, understanding and using BIFs is a very integral part of Oracle tuning. Oracle has many built-in functions and Oracle also allows you to define your own function and make it a part of your own SQL dialect. You can see all built-in Oracle functions with this query:

select distinct 
  package_name = 'STANDARD'; 

Here are my notes on common SQL built-in functions:
  1. add_months
  2. avg
  3. chr(n)
  4. concat(s1,s2)
  5. convert(char_to_convert, new_char_set, old_char_set)
  6. count(*)
  7. decode
  8. floor
  9. greatest(expression, expression...)
  10. grouping
  11. initcap(char) string with initial capitals
  12. instr
  13. last_day(date)returns the last day of month in date
  14. least(expression, expression...)
  15. length(char)
  16. lower(char) lpad(char, n[,padchar])
  17. ltrim(char[,set])
  18. max([distinct] expr)
  19. min([distinct] expr)
  20. mod(x,y)
  21. months_between(end_date, start_date)
  22. next_day(date,day_of_week)
  23. nvl(expression, value_if_null)
  24. rank
  25. replace(char, search_str[, replace_str])
  26. round
  27. rowidtochar
  28. rpad
  29. rtrim
  30. soundex(char)
  31. substr
  32. sys_context('namespace','attribute_name')
  33. to_lob
  34. to_number
  35. to_date
  36. to_char
  37. translate
  38. trim
  39. trunc
  40. upper
  41. user
  42. userenv