FormatString Function

The FormatString() function works same as .NET Framework composite formatting feature. The function takes a list of arguments and a composite format string as input. A composite format string consists of fixed text intermixed with indexed placeholders, called format items, that correspond to the objects in the list. The formatting operation returns a result string that consists of the original fixed text intermixed with the string representation of the objects in the list. Unlike String.Format function of .NET, SQL Database Library require minimum of three 3 arguments. It starts with Culture Name and all formatting is done using that culture.

The syntax for Stuff function is FormatString( Culture Name , String to Format , Arguments.... ). The arguments can be Column Names


Each format item "{N}" takes the following form and consists of the following components:
{ index [ ,alignment ] [ :formatString ] }

The matching braces ("{" and "}") are required. The mandatory index component, is a number starting from 0 that identifies a corresponding item in the list of objects.

/* Following SQL returns Even numbers less than 10: 2, 4, 6, 8  */ 
SELECT FormatString('en-US', 'Even numbers less than 10: {0}, {1}, {2}, {3} ' , 2, 4, 6, 8 ) ;

/* Following SQL returns Order Total $10.75  */ 
SELECT FormatString('en-US', 'Order Total {0:C2}' , 10.75 ) ;

/* Following SQL returns {1234}  */ 
SELECT FormatString('en-US', '{0}{1:D}{2}' , '{', 1234 , '}') ;

/* Following SQL returns Today is 7/1/2017 2:21:40 PM or the local system date */
 SELECT FormatString('en-US', 'Today is {0}' , GetDate()) ;	

It provides developers great flexibility over the output of the string in a way that is easier to read, write and maintain than just using plain concatenation. FormatString support localization of international languages and can be stored in database tables to provide templates which can be updated more easily than updating front end concatenation.