SQL Tutorial 10: SQL Functions

SQL Tutorial For Beginners
SQL Tutorial 10: SQL Functions

1) What is a Function?

A Function is a predefined formula which takes one or more arguments as input,
then process the input, and 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 one Database Management System to another,
Database Management Systems,
Oracle,
MS SQL Server,
MySQL,
SyBase etc...
---------------------
Note: In this session we are going to discuss MS SQL Server Functions...

SQL Server has different types of Functions

i) String Functions - These Functions are only work on String type data

ii) Numeric Functions - These Functions are only work on Numeric data type

iii) Date Functions - These Functions are only work on Date data type

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

1) Concat

Concatenates two or more 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 s 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 in a string with another set of characters

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

0 comments:

Post a Comment