FORMAT Function in SQL

This string function of Structured Query Language changes the format of the given text. In SQL, it can change the string format into any format.

Syntax of FORMAT String Function

Syntax1: This syntax uses the FORMAT function with the column name of the SQL table:

In the syntax, we have to specify the name of those columns whose values we want to FORMAT.

Syntax2: This syntax uses the FORMAT function with string:

Examples of FORMAT String function

Example 1: The following SELECT query changes the string into a percentage:

Output:

Percent_98

98%

Example 2: The following SELECT query changes the string into currency amount:

Output:

Percent_98

98.00

Example 3: The following SELECT query converts the string into the custom string format:

Output:

Custom_#

$9,812.00

Example 4: The following SELECT query converts the date into the specified format:

Output:

Format_M/d/yyyy

4 / 11 / 2022

Example 5: The following SELECT query converts the time in the specified format:

Output:

Format_hh:mm:ss’

2: 50 : 38 PM

Example 6: This example uses the FORMAT function with the table in Structured Query Language.

In this example, we will create a new SQL table on which we want to execute the Format function.

The syntax for creating the new table in the SQL database is as follows

  CREATE TABLE table_name  (  First_Column_of_table Data Type (character_size of First Column),    Second_Column_of_table Data Type (character_size of the Second column ),    Third_Column_of_table Data Type (character_size of the Third column),    …      Last_Column_of_table Data Type (character_size of the Last column)  );    

The following CREATE statement creates the Worker_Grade table:

  CREATE TABLE Worker_Grade  (  Worder_ID Varchar(50) PRIMARY KEY,    First_Name VARCHAR (100),    Last_Name VARCHAR (100),   First_City Varchar(120),  Second_City Varchar(120),  New_City Varchar(120),  Attendance_Remarks Varchar(50),   Worker_Salary Varchar(50),   Grade Varchar (80)  );    

The below INSERT queries insert the records of Workers with Grades, Salaries, and Remarks in the Worker_Grade table:

  INSERT INTO Worker_Grade (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) VALUES (10, Aman, Sharma, Lucknow Chandigarh, Ghaziabad, 88, 9598, A2);    INSERT INTO Worker_Grade   (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   VALUES ( 02, Vishal, Sharma, Chandigarh, Ghaziabad, Delhi, 95, 8200, A1 );    INSERT INTO Worker_Grade   (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   VALUES (07, Raj, Gupta, Delhi, Ghaziabad, Lucknow, 91, 9512, A1);    INSERT INTO Worker_Grade   (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)    VALUES (04, Yash, Singhania, Ghaziabad, Delhi, Lucknow, 85, 8225, A2);    INSERT INTO Worker_Grade   (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   VALUES (11, Vinay, Roy, Delhi, Kanpur, Ghaziabad, 95, 9712, A1);    INSERT INTO Worker_Grade   (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   VALUES (16, Manoj, Gupta, Ghaziabad, Meerut, Chandigarh, 95, 9025, B1);      INSERT INTO Worker_Grade   (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)   VALUES (19, Ram, Gupta, Lucknow, Ghaziabad, Chandigarh, 89, 9512, A2);  

The following SELECT statement displays the inserted records of the above Worker_Grade table:

Worder_ID	First_Name	Last_Name	First_City	Second_City	New_City	Attendance_Remarks	Work_Remarks	Grade
10	Aman	Sharma	Lucknow	Chandigarh	Ghaziabad	88	95	A2
02	Vishal	Sharma	Chandigarh	Ghaziabad	Ghaziabad	95	82	A1
07	Raj	Gupta	Delhi	Ghaziabad	Lucknow	91	95	A1
04	Yash	Singhania	Ghaziabad	Delhi	Lucknow	85	82	A2
11	Vinay	Roy	Delhi	Kanpur	Ghaziabad	95	97	A1
16	Manoj	Gupta	Ghaziabad	Meerut	Chandigarh	95	90	B1
19	Ram	Gupta	Lucknow	Ghaziabad	Chandigarh	89	95	A2

Query 1: The following SELECT query uses the FORMAT function with the Worker_ID column of the above Worker_Grade table:

SELECT Worker_ID, Format(Worker_ID, C) AS Format_c FROM Worker_Grade;

This SQL statement changes the format of the id of each worker.

Output:

Worker_ID	Format_C
10	10.00
02	2.00
07	7.00
04	4.00
11	11.00
16	16.00
19	19.00

Query 2: The following SELECT query uses the FORMAT function with the Attendance_Remarks column of the above Worker_Grade table:

  SELECT Attendance_Remarks, Format(Attendance_Remarks, Percent) AS Format_percent FROM Worker_Grade;  

This SQL statement changes the format of the Attendance remarks of each worker.

Output:

Attendance_Remarks	Format_Percent
88	88%
95	95%
91	91%
85	85%
95	95%
95	95%
89	89%

Query 3: The following SELECT query uses the FORMAT function with the Worker_Salary column of the above Worker_Grade table:

  SELECT Worker_Salary, Format(Worker_Salary, ‘$#,###.00’ ) AS Format_Default FROM Worker_Grade;  

This SQL statement changes the format of the salary of each worker.

Output:

Worker_Salary	Format_Default
9598	9,598.00
8200	8,200.00
9512	9,512.00
8225	8,225.00
9712	9,712.00
9025	9,025.00
9512	9,512.00

Next TopicINSERT Function in SQL

FORMAT Function in SQL

FORMAT Function in SQL

Syntax of FORMAT String Function

Examples of FORMAT String function

SQL Server RENAME Database

How to Delete Column from Table in SQL

You may also like