SQL Tutorial 10: SQL Functions

SQL Tutorial 10: SQL Functions

1) What is a Function?

A Function is a predefined formula which takes 1 or to a greater extent than arguments every bit input,
then procedure the input, as well as returns an output.
------------------------------
2) Different types of SQL Functions....

> SQL has many built-in functions for performing calculations on data, SQL Functions
may vary from 1 Database Management System to another,
Database Management Systems,
Oracle,
MS SQL Server,
MySQL,
SyBase etc...
---------------------
Note: In this session nosotros are going to hash out MS SQL Server Functions...

SQL Server has unlike types of Functions

i) String Functions - These Functions are solely piece of job on String type data

ii) Numeric Functions - These Functions are solely piece of job on Numeric information type

iii) Date Functions - These Functions are solely piece of job on Date information type

iv) Coversion Functions etc...
-------------------------------------------------
i) SQL String Functions

1) Concat

Concatenates 2 or to a greater extent than strings together

Example:
Select Concat ('gcreddy', '.com');
Output: gcreddy.com
-------------------------------------------------
2) Len

Returns the length of the specified String

Example:
Select LEN ('gcreddy.com');
Output: 11
-------------------------------------------------
3) Left

Extracts a Substring from a String (Starting from left)

Example:
Select Left('SQL Tutorial', 3);
Output: SQL
-------------------------------------------------
4) Right

Extracts a Subsisting from a String (Starting from right)

Example:
Select Right ('SQL Tutorial', 3);
Output: ial
-------------------------------------------------
5) Lower 

Converts a string to lower case

Example:
Select Lower('SQL');
Select Lower('sql');
Select Lower('SqL');
Select Lower('SQL123');
Output:
sql
sql
sql
sql123
-------------------------------------------------
6) Upper

Converts a string to upper case

example:
Select Upper('SQL');
Select Upper('sql');
Select Upper('SqL');
Select Upper('SQL123');
Output:
SQL
SQL
SQL
SQL123
-------------------------------------------------
7) LTrim

Removes leading spaces from a string 

Example:
Select ('        GCREDDY');
Select LTRIM('        GCREDDY');
Output:
      GCREDDY
GCREDDY
-------------------------------------------------
8) RTrim

Removes trailing spaces from a string

Example:
Select Rtrim('SQL Tutorial                    ');
Output: SQL Tutorial
-------------------------------------------------
9) SubString

Extracts second substring from a string

Example:
Select SubString('SQL Tutorial', 1, 3);--SQL
Select SubString('SQL Tutorial', 5, 3);--Tut
Select SubString('SQL Tutorial', 3, 3);--L T
Output:
SQL
Tut
L T
-------------------------------------------------
10) Replace

Replaces a sequence of characters inwards a string alongside roughly other fix of characters

Example:
Select REPLACE('SQL Tutorial', 'SQL', 'Java');--Java Tutorial
Output: 
Java Tutorial
-------------------------------------------------

Sumber http://www.gcreddy.com/