SQL functions in DQL

Functions are very powerful feature of SQL which are used to do the followings:

Functions are small purpose programs, which may accept argument and return value. Basically there are two types of SQL functions such as :

Let's see some of the properties of single row functions:
Operates on data items:

Single row functions are of different types like:

Character functions: input-character output-character or number
Number functions: input-numeric output-numeric
Date functions : input-date output-date or number
Conversion functions: input-any output-any
Character functions

Lower(column| expression)- converts character values to lower case.

Example:

select lower(emp-name)
From employee ;

The above example will display the names of the employee in the employee table in lower case.
Upper(column|expression):- converts character values to uppercase.

Example:

Select upper(emp_name)
From employee;

The above example will display the names of employee in the employee table in uppercase.
Initcap(column|expression):- converts the first character of each word to uppercase and the rest of the character to lowercase.

Example:

Select initcap(emp_name)
From employee;

The above example will display the names of the employee in the employee table in Title case.
Concate(column1|expression1, column2|expression2):- joins values together. It can take two arguments at a time.

Example:

Select concate('Mr', emp_name)
From employee;

The above example will display all the employee names in the employee table preceded by Mr.
Substr(column|expression, x, y):- returns specified characters from character value string given as argument -1 at character position x and y characters long.

Example:

Substr('helloworld', 6, 5);

The result is world.
Length(column|expression):- the length function returns the number of character in the expression.

Example:

Select emp_name, length(emp_name)
From employee;

The above example will display to columns of value in the query output output one will consist of all the employee names and the other will display the number of character of each time.
Note:the count starts from 1.
Instr(column|expression, 'str', x, y):- returns the numeric position of a character or word specified in the second argument(str) in the first argument. You can also provide a position x to start searching and the occurrence y of the string. The default value of x and y is 1 means the search starts from the beginning and reports the first occurrence.

Example:

Instr('helloworld', 'r')

The result is 8.
lpad(column|expression, x, 'str'):- pads the character value passed in the tird argument right justified to a total width of x character position.

Example:

Lpad(15000, 10, '$')

The result is $$$$$15000

Lpad(150000, 10, '$')

The result is $$$$150000
Rpad(column|expression, x, 'str'):- pads the character value passed in the third argument left justified to a total width of x character position.

Example:

The result is $$$$$15000

Rpad(15000, 10, '$')

The result is 15000$$$$$
Trim(leading|trailing|both, trim character from trim source)- enables you to trim heading or trailing characters or both from a character string . in case the trim character or the trim source is a character literal, you must enclose it in single quote.

Example:

Trim('h' from 'helloworld')

The result is elloworld.
replace(string, search string, replacement string)- this function searches a string for a character string. If found replaces it with a specified replacement string.



  Tutorials

Popular Links

Contact Us

address Plot No-741,2ND Floor
Opp. Bhagabati Temple,Jayadev Vihar
      Bhubaneswar-751013
      Email: info@silantechnology.com
      Phone: 0674-2361252
address
39877, sundale dr, apt#101, Fremont, California 94538, USA
       Phone: +1(262)388-7619

facebook twitter google linked in

© 2018 Silan Technology. All Rights Reserved