How to create custom formatting, conversion or validation using scalar functions

Problem: How to create a custom conversion or validation check of an input value.

Solution: Scalar - Valued Functions.

CREATE FUNCTION

User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multi statement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Example:

Let’s try few examples on what we can do with scalar functions.

Some of us always try to format phone numbers or social security numbers to an easy look, So I am going to show you how we can convert an integer to a stylish format using stuff function. We can go as fancy as we want based on the set logic, u can also use conditional case statements to do validation checks and convert accordingly.

Please see the below sample code for a scalar function to format the phone number.

CREATE FUNCTION [dbo].[FormatPhoneNumber]

(@Phone bigint )

RETURNS varchar(max)

AS

BEGIN

                DECLARE @Text varchar(15) = ''

                SET @Text =  stuff(stuff(stuff(stuff(@Phone, 1, 0, '('),5, 0, ')'), 6, 0, ' '), 10, 0, '-')                        

                                                 

RETURN @Text

END

GO

SELECT  [CallId]

      ,[CallerName]

      ,[Location]

     ,[phone]

      ,[dbo].[FormatPhoneNumber]([Phone]) as [formatted phone number]

      ,[Status]

  FROM [dbo].[T_Call]

Output: