Functions are the measured values and cannot create permanent environment changes to SQL server. SQL functions are used for the following purpose:

• To perform calculations on data
• To modify individual data items
• To manipulate the output
• To format dates and numbers
• To convert data types

Case manipulation functions are the functions which convert the data from the state in which it is already stored in the table to upper, lower or mixed case.

Case manipulation function can be used in almost every part of the SQL statement.

Case manipulation functions are mostly used when you need to search for data, and you don?t have any idea that the data you are looking for is in lower case or upper case.

There are three case manipulation functions in SQL:

• LOWER: converts character into Lowercase.
• UPPER: converts character into uppercase.
• INITCAP: converts character values to uppercase for the initials of each word.

Character-manipulation functions are used to change, extract, alter the character string.

One or more than one characters and words should be passed into the function, and then the function will perform its operation on those words.

• CONCAT: join two or more values together.
• SUBSTR: used to extract the string of specific length.
• LENGTH: return the length of the string in numerical value.
• INSTR: find the exact numeric position of a specified character.
• LPAD: padding of the left-side character value for right-justified value.
• RPAD: padding of right-side character value for left-justified value.
• TRIM: remove all the defined character from the beginning, end or both beginning and end.
• REPLACE: replace a specific sequence of character with other sequences of character.

The NVL() function is used to convert NULL value to the other value. NVL() function is used in Oracle it is not in SQL and MySQL server.

Instead of NVL() function MySQL have IFNULL() and SQL Server have ISNULL() function.

The MOD function returns the remainder in a division operation.

The syntax of COALESCE function:

The COALESCE function is used to return the first non-null expression given in the parameter list.

The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value. The DISTINCT keyword is used to SELECT DISTINCT, and it always fetches different (distinct) from the column of the table.