Scalar-valued

---

Scalar-valued User Defined Functions in SQL Server

The functions which return only a single value (scalar value) from the table is known as scalar value function. Scalar value functions may or may not have parameters which are optional, but always return a single (scalar) value which is mandatory.

Types of scalar functions used most commonly are listed below:

• 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
  
  

  1.The UCASE() Function

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

  Syntax

  SELECT UCASE(column_name) FROM table_name
  

  Example

  We consider the following "Persons" table:

  P_Id	LastName	FirstName	Address	City
  1	Shanmuk	Ola	Ramnagar-25	Hyderabad
  2	Thanush	Ayan	Old city-10	Ahmedabad
  3	Suhansh	Batta	Rajiv nagar-15	Jodhpur

  Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to uppercase,using the following SELECT statement.

  SELECT UCASE(LastName) as LastName,FirstName FROM Persons
  

  This would produce the following result:

  Lastname	Firstname
  SHANMUK	Ola
  THANUSH	Ayan
  SUHANSH	Batta

  
  

  2.SQL LCASE() Function

  The LCASE() function in SQL converts the value of a field to lowercase.

  Syntax

  SELECT LCASE(column_name) FROM table_name
  

  Example

  We consider the following "Persons" table:

  P_Id	LastName	FirstName	Address	City
  1	Shanmuk	Ola	Ramnagar-25	Hyderabad
  2	Thanush	Ayan	Old city-10	Ahmedabad
  3	Suhansh	Batta	Rajiv nagar-15	Jodhpur

  Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to lowercase,using the following SELECT statement.

  SELECT LCASE(LastName) as LastName,FirstName FROM Persons
  

  This would produce the following result:

  Lastname	Firstname
  shanmuk	Ola
  thanush	Ayan
  suhansh	Batta

  
  

  3.SQL MID() Function

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

  Syntax

  SELECT MID(column_name,start[,length]) FROM table_name
  
  

  Parameter	Description
  column_name	Required. The field to extract characters from.
  start	Required. Specifies the starting position (starts at 1).
  length	Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text.

  Example

  We consider the following "Persons" table:

  P_Id	LastName	FirstName	Address	City
  1	Shanmuk	Ola	Ramnagar-25	Hyderabad
  2	Thanush	Ayan	Old city-10	Ahmedabad
  3	Suhansh	Batta	Rajiv nagar-15	Jodhpur

  Now we want to extract the first four characters of the "City" column,using the following SELECT statement:

  
  SELECT MID(City,1,4) as SmallCity FROM Persons
  

  This would produce the following result:

  Smallcity
  Hyde
  Ahme
  Jodh

  
  

  4.SQL LEN() Function

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

  Syntax

  
  SELECT LEN(column_name) FROM table_name
  

  Example

  we consider the following "persons" table:

  P_Id	LastName	FirstName	Address	City
  1	Shanmuk	Ola	Ramnagar-25	Hyderabad
  2	Thanush	Ayan	Old city-10	Ahmedabad
  3	Suhansh	Batta	Rajiv nagar-15	Jodhpur

  Now we want to select the length of the values in the "Address" column using the following SELECT statement:

  
  SELECT LEN(Address) as LengthOfAddress FROM Persons
  

  This would produce the following result:

  LengthOfAddress
  11
  11
  14

  
  

  5.SQL ROUND() Function

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

  Syntax

  SELECT ROUND(column_name,decimals) FROM table_name
  

  Parameter	Description
  column_name	Required. The field to round.
  decimals	Required. Specifies the number of decimals to be returned.

  Example

  We consider the following "Products" table:

  Prod_Id	ProductName	Unit	UnitPrice
  1	waterbottle	1000ml	95.45
  2	Sago granules	1000g	55.71
  3	Nutella	1000g	1050.65

  
  

  Now we want to display the product name and the price rounded to the nearest integer,using the following SELECT statement:

  SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
  

  This would produce the following result:

  ProductName	UnitPrice
  Waterbottle	95
  Sago granules	56
  Nutella	1051

  
  

  6.SQL NOW() Function

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

  Syntax

  SELECT NOW() FROM table_name
  

  Example

  We consider the following "Products" table:

  Prod_Id	ProductName	Unit	UnitPrice
  1	waterbottle	1000ml	95.45
  2	Sago granules	1000g	55.71
  3	Nutella	1000g	1050.65

  Now we want to display the products and prices per today's date,using the following SELECT statement:

  
  SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
  

  This would produce the following result:

  ProductName	UnitPrice	PerDate
  Waterbottle	95.45	21/8/2019 11:05:45 AM
  Sago granules	55.71	21/8/2019 11:05:45 AM
  Nutella	1050.65	21/8/2019 11:05:45 AM

  
  

  7.SQL FORMAT() Function

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

  Syntax

  SELECT FORMAT(column_name,format) FROM table_name
  

  Parameter	Description
  column_name	Required. The field to be formatted.
  format	Required. Specifies the format.

  Example

  We consider the following "Products" table:

  Prod_Id	ProductName	Unit	UnitPrice
  1	waterbottle	1000ml	95.45
  2	Sago granules	1000g	55.71
  3	Nutella	1000g	1050.65

  Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD"),using the following SELECT statement:

  
  SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
  FROM Products
  

  This would produce the following result:

  ProductName	UnitPrice	PerDate
  Waterbottle	95.45	2019/8/21
  Sago granules	55.71	2019/8/21
  Nutella	1050.65	2019/8/21

  
  
  

  Connection String

  SQL Server Connection String Formats

  posted on 2019-08-27 05:53:53 - SQL Server Tutorials

  
  

  UNION

  union in SQL server

  posted on 2019-08-09 23:05:33 - SQL Server Tutorials

  
  

  System Functions

  System Functions in SQL Server

  posted on 2019-08-09 06:14:33 - SQL Server Tutorials

  
  

  Prompt Examples

  ChatGPT Prompt Examples

  posted on 2023-06-21 22:37:19 - ChatGPT Tutorials

  
  

  Use Cases

  Chat GPT Key Use Cases

  posted on 2023-06-21 21:03:17 - ChatGPT Tutorials

  
  

  Prompt Frameworks

  Prompt Frameworks

  posted on 2023-06-21 19:33:06 - ChatGPT Tutorials