Working with SQL Functions


SQL Functions: A function is a special type of command word in the SQL99 command set. In effect, functions are one-word commands that return a single value. The value of a function can be determined by input parameters, as with a function that averages a list of database values. But many functions do not use any type of input parameter, such as the function that returns the current system

time, CURRENT_TIME.

 

Function
Syntax
Example
Result
Working With Character
LOWER
LOWER (expression)
LOWER (‘SQL Course’)
sql course
UPPER
UPPER (expression)
UPPER (‘SQL Course’)
SQL COURSE
INITCAP
INITCAP (expression)
INITCAP (‘SQL course’)
Sql Course
CONCAT
CONCAT (expression1, expression2)
CONCAT (‘Hello’, ‘World’)
HelloWorld
SUBSTR
SUBSTR (expression, m[,n])
SUBSTR (‘HelloWorld’,1,5)
Hello
LENGTH
LENGTH (expression)
LENGTH (‘HelloWorld’)
10
INSTR
INSTR (expression, ‘string’,[,m],[n])
INSTR (‘Bangladesh’, ‘a’, 1, 2)
6
LPAD
LPAD (expression, n, ‘string’)
LPAD (salary, 10, ‘*’)
*****24000
RPAD
RPAD (expression, n, ‘string’)
RPAD (salary, 10, ‘*’)
24000*****
TRIM
TRIM (leading | both, trim_character from trim_source)
TRIM (‘H’, from ‘HelloworldHHH’)
elloworld
REPLACE
REPLACE (text, search_string, replacement_string)
REPLACE (‘JACK and JUE’, ‘J’, ‘BL’)
BLACK and BLUE
Working With Number
ROUND
ROUND (expression, n)
ROUND (45.926, 2)
45.93
TRUNC
TRUNC (expression, n)
TRUNC (45.926, 2)
45.92
MOD
MOD (m, n)
MOD (1600, 300)
100
MONTHS_BETWEEN
MONTHS_BETWEEN (date1, date2)
MONTHS_BETWEEN (’13-Sep-13’,’12-Sep-1995’)
216.064007
Working With Date & Time
ADD_MONTHS
ADD_MONTHS (date, n)
ADD_MONTHS (’11-JAN-95’, 6)
11-JUL-95
NEXT_DAY
NEXT_DAY (date, ‘char’)
NEXT_DAY (’01-SEP-95’, ‘FRIDAY’)
08-SEP-95
LAST_DAY
LAST_DAY (date)
LAST_DAY (’01-FEB-95’)
28-FEB-95
ROUND
ROUND (date[, ‘fmt’])
ROUND (SYSDATE, ‘MONTH’)
01-SEP-13
TRUNC
TRUNC (date[, ‘fmt’])
TRUNC (SYSDATE, ‘YEAR’)
01-JAN-13

You may also like

3 Comments

  • Zachary
    September 5, 2014 at 8:06 pm

    I read a lot of interesting posts here. Probably you spend a lot of time writing, i know how to save you a lot of time, there is an online tool that creates readable, SEO friendly posts in seconds, just search in google.

  • Novella
    September 10, 2014 at 12:53 pm

    It was difficult to find your website in google. I found it on 11 place, you should spread your articles to social sites ,
    it will help you to get much more visitors. You can do it on autopilot,
    i know how to help you, just search in google – k2 seo
    tips and tricks

  • Sophie
    September 11, 2014 at 4:52 pm

    I read a lot of interesting articles here. Probably you
    spend a lot of time writing, i know how to save you a lot of
    time, there is an online tool that creates
    high quality, google friendly posts in minutes, just type in google
    – laranitas free content source

Comments are closed here.

Follow Me

Oracle Certification