SQL Functions

String  Functions

a) Upper Function: Upper function is used to convert all the characters in upper case.

Syntax:

select upper(field) from table;

b) Lower Function:  Lower function is used to convert all character in lower case of field/string.

Syntax:

 select lower(field) from table;

c) Concat Function: Concat function is used to concatenate for than one filed/string.

Syntax:

select concat(field1,field2) from table

d) Length Function: Length function is used to get the length of field/string value.

Syntax:

select length(field) from table

e) Trim Function: Trim Function is used for remove trailing or leading space from field/string .

Syntax:

select trip(field) from table;

f) Substring Function: Substring function is used for extract a substring from field/string

Syntax:

 select substring(field, start position, length) from table

g) Replace Function: Replace function is used to replace a substring in string with other substring.

Syntax:

 select replace(field, old_substring, new_substring)

Date Function

a) GETDATE function: GetDate function is used to get the current date and time.

Syntax:

select GETDATE() from table;

b) DATEPART Function: To extract the specific part of date DATEPART Function is used .

Syntax:

 select dateprt(part, date) from table;

c) DATEADD Function: DATEADD function is used to add a specific number of unit a date value.

Syntax:

select dateadd(unit, value, date) from table

d) DATEDIFF Function: To calculate the difference of two date datediff function is used.

Syntax:

select datediff(unit, start_date, end_date) from table

e) Year Function : Year function is used to extract the year from date.

Syntax:

select year(date field) from table;

Aggregate Function

a) Count Function: Count Function is used to get the number of rows in a table.

Syntax:

select count(field) from table

b) SUM Function: SUM Function is used to get the sum of numeric field value.

Syntax:

select sum(field) from table

c) AVG Function: AVG Function is used to get the average of numeric field value.

Syntax:

select avg(field) from table;

d) MIN Function: MIN Function is used to get the minimum value.

Syntax:

select min(field) from table;

e) MAX Function: MAX Function is used to get the maximum value.

Syntax:

select max(field) from table;

Keep Learning 🙂