sql scalar functions:-

SQL scalar functions return a single value, based on the input value.

• UCASE() - Converts a field to upper case

• LCASE() - Converts a field to lower case

• MID() - Extract characters from a text field

• LEN() - Returns the length of a text field

• ROUND() - Rounds a numeric field to the number of decimals specified

• NOW() - Returns the current system date and time

• FORMAT() - Formats how a field is to be displayed

UCASE(): It converts the value of a field to uppercase.

• Syntax:

  SELECT UCASE(column_name) FROM table_name;
  

  Queries:

  1. Converting names of students from the table Students to uppercase.

     SELECT UCASE(NAME) FROM Students;
     

     Output:

     | NAME | | :--- | | HARSH | | SURESH | | PRATIK | | DHANRAJ | | RAM |

• LCASE(): It converts the value of a field to lowercase.
  Syntax:

  SELECT LCASE(column_name) FROM table_name;
  

  Queries:

  1. Converting names of students from the table Students to lowercase.

     SELECT LCASE(NAME) FROM Students;
     

     Output:

     | NAME | | :--- | | harsh | | suresh | | pratik | | dhanraj | | ram |

• MID(): The MID() function extracts texts from the text field.
  Syntax:

  SELECT MID(column_name,start,length) AS some_name FROM table_name;
  
  specifying length is optional here, and start signifies start position ( starting from 1 )
  

  Queries:

  1. Fetching first four characters of names of students from the Students table.

     SELECT MID(NAME,1,4) FROM Students;
     

     Output:

     | NAME | | :--- | | HARS | | SURE | | PRAT | | DHAN | | RAM |

• LEN(): The LEN() function returns the length of the value in a text field.

• Syntax:

  SELECT LENGTH(column_name) FROM table_name;
  

  Queries:

  1. Fetching length of names of students from Students table.

     SELECT LENGTH(NAME) FROM Students;
     

     Output:

     | NAME | | :--- | | 5 | | 6 | | 6 | | 7 | | 3 |

• ROUND(): The ROUND() function is used to round a numeric field to the number of decimals specified.NOTE: Many database systems have adopted the IEEE 754 standard for arithmetic operations, which says that when any numeric .5 is rounded it results to the nearest even integer i.e, 5.5 and 6.5 both gets rounded off to 6.
  Syntax:

  SELECT ROUND(column_name,decimals) FROM table_name; 
  
  decimals- number of decimals to be fetched.
  

  Queries:

  1. Fetching maximum marks among students from the Students table.

     SELECT ROUND(MARKS,0) FROM table_name;
     

     Output:

     | MARKS | | :--- | | 90 | | 50 | | 80 | | 95 | | 85 |

• NOW(): The NOW() function returns the current system date and time.
  Syntax:

  SELECT NOW() FROM table_name;
  

  Queries:

  1. Fetching current system time.

     SELECT NAME, NOW() AS DateTime FROM Students;
     

     Output:

     | NAME | DateTime | | :--- | :--- | | HARSH | 1/13/2017 1:30:11 PM | | SURESH | 1/13/2017 1:30:11 PM | | PRATIK | 1/13/2017 1:30:11 PM | | DHANRAJ | 1/13/2017 1:30:11 PM | | RAM | 1/13/2017 1:30:11 PM |

• FORMAT(): The FORMAT() function is used to format how a field is to be displayed.
  Syntax:

  SELECT FORMAT(column_name,format) FROM table_name;
  

  Queries:

  1. Formatting current date as ‘YYYY-MM-DD’.

     SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Students;
     

     Output:

     | NAME | Date | | :--- | :--- | | HARSH | 2017-01-13 | | SURESH | 2017-01-13 | | PRATIK | 2017-01-13 | | DHANRAJ | 2017-01-13 | | RAM | 2017-01-13 |