SQL - SCALER FUNCTIONS

 

The following are the most commonly used SQL aggregate functions:

  • 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 ()

The UCASE () function converts the value of a field to uppercase.

SYNTAX

200.png

SYNTAX FOR SQL Server

201.png

EXAMPLE 

Let’s consider a demo database and table SALARYINFO

202.png

Figure 49.1. SALARYINFO TABLE

QUERY

203.png

OUTPUT

204.png

LCASE ()

The LCASE () function converts the value of a field to uppercase.

SYNTAX

205.png

SYNTAX FOR SQL Server

206.png

EXAMPLE

Let’s consider a demo database and table SALARYINFO

207.png

Figure 1Figure 49.2. SALARYINFO TABLE

QUERY

208.png

OUTPUT

209.png
 
 

MID ()

The MID () function is used to extract characters from a text field.

SYNTAX

210.png

SYNTAX FOR SQL Server

211.png

LEN ()

THE LEN() function returns the length of the value in a text field.

SYNTAX

212.png

SYNTAX FOR SQL Server

213.png

EXAMPLE

Let’s consider a demo database and table SALARYINFO

214.png

Figure 49.3. SALARYINFO TABLE

QUERY

216.png
 
215.png

OUTPUT

ROUND ()

The ROUND () function is used to round a numeric field to the number of decimals specified.

SYNTAX

217.png
 

NOW ()

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

SYNTAX

218.png
 

FORMAT ()

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

SYNTAX

219.png