SQL----Scalar Functions

UCASE () function

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

SQL UCASE () syntax

SELECT UCASE (column_name) from table_name

SQL UCASE () instance

We have the following "Persons" table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to select the contents of the "LastName" and "FirstName" columns, and then convert the "LastName" column to uppercase.

We use the following SQL statement:

SELECT UCASE (LastName) as Lastname,firstname from Persons

The result set looks like this:

LastName	FirstName
Adams	John
BUSH	George
CARTER	Thomas

LCASE () function

The LCASE function converts the value of the field to lowercase.

SQL LCASE () syntax

SELECT LCASE (column_name) from table_name

SQL LCASE () instance

We have the following "Persons" table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to select the contents of the "LastName" and "FirstName" columns, and then convert the "LastName" column to lowercase.

We use the following SQL statement:

SELECT LCASE (LastName) as Lastname,firstname from Persons

The result set looks like this:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

MID () function

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

SQL MID () syntax

SELECT MID (Column_name,start[,length]) from table_name

Parameters	Description
column_name	Necessary. The field to extract the characters from.
Start	Necessary. Specifies the starting position (the starting value is 1).
Length	Optional. The number of characters to return. If omitted, the MID () function returns the remaining text.

SQL MID () instance

We have the following "Persons" table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now, we want to extract the first 3 characters from the City column.

We use the following SQL statement:

SELECT MID (city,1,3) as smallcity from Persons

The result set looks like this:

Smallcity
Lon
New
Bei

LEN () function

The LEN function returns the length of the value in the Text field.

SQL LEN () syntax

SELECT LEN (column_name) from table_name

SQL LEN () instance

We have the following "Persons" table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now, we want to get the length of the value in the City column.

We use the following SQL statement:

SELECT LEN (city) as lengthofcity from Persons

The result set looks like this:

Lengthofcity
6
8
7

ROUND () function

The ROUND function is used to round a numeric field to a specified number of decimal digits.

SQL ROUND () syntax

SELECT ROUND (column_name,decimals) from table_name

Parameters	Description
column_name	Necessary. The field to be rounded.
Decimals	Necessary. Specifies the number of decimal digits to return.

SQL ROUND () instance

We have the following "Products" table:

prod_id	ProductName	Unit	UnitPrice
1	Gold	+ G	32.35
2	Silver	+ G	11.56
3	Copper	+ G	6.85

Now, we want to round up the name and price to the nearest integer.

We use the following SQL statement:

SELECT ProductName, ROUND (unitprice,0) as UnitPrice from products

The result set looks like this:

ProductName	UnitPrice
Gold	32
Silver	12
Copper	7

Now () function

The NOW function returns the current date and time.

Tip: If you are using a SQL Server database, use the GETDATE () function to get the current DateTime.

SQL Now () syntax

SELECT now () from table_name

SQL Now () instance

We have the following "Products" table:

prod_id	ProductName	Unit	UnitPrice
1	Gold	+ G	32.35
2	Silver	+ G	11.56
3	Copper	+ G	6.85

Now, we want to show the name and price of the day's date.

We use the following SQL statement:

SELECT ProductName, UnitPrice, now () as Perdate from products

The result set looks like this:

ProductName	UnitPrice	perdate
Gold	32.35	12/29/2008 11:36:05 AM
Silver	11.56	12/29/2008 11:36:05 AM
Copper	6.85	12/29/2008 11:36:05 AM

FORMAT () function

The Format function is used for formatting the display of a field.

SQL FORMAT () syntax

SELECT FORMAT (Column_name,format) from table_name

Parameters	Description
column_name	Necessary. The field to format.
Format	Necessary. prescribed format.

SQL FORMAT () instance

We have the following "Products" table:

prod_id	ProductName	Unit	UnitPrice
1	Gold	+ G	32.35
2	Silver	+ G	11.56
3	Copper	+ G	6.85

Now, we want to show the name and price of each day's date (the date is displayed in the format "YYYY-MM-DD").

We use the following SQL statement:

SELECT ProductName, UnitPrice, FORMAT (now (), ' Yyyy-mm-dd ') as Perdatefrom products

The result set looks like this:

ProductName	UnitPrice	perdate
Gold	32.35	12/29/2008
Silver	11.56	12/29/2008
Copper	6.85	12/29/2008

SQL----Scalar Functions