If you are using SQL Server 2012 and above, you can use FORMATMESSAGE. eg.
DECLARE @s NVARCHAR(50) = 'World';
DECLARE @d INT = 123;
SELECT FORMATMESSAGE('Hello %s, %d', @s, @d)
-- RETURNS 'Hello World, 123'
More examples from MSDN: FORMATMESSAGE
SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with leading zero %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
NOTES:
- Undocumented in 2012
- Limited to 2044 characters
- To escape the % sign, you need to double it.
- If you are logging errors in extended events, calling
FORMATMESSAGEcomes up as a (harmless) error
W3Schools
w3schools.com › sql › func_sqlserver_format.asp
SQL Server FORMAT() Function
String Functions: ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Date Functions: ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DA
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › format-transact-sql
FORMAT (Transact-SQL) - SQL Server | Microsoft Learn
Applies to: SQL Server Azure SQL ... the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings....
Videos
13:40
Prettify Your SQL Outputs with the FORMAT Function - YouTube
05:23
SQL FORMAT() Function - YouTube
10:55
Converting numbers to strings in SQL Server: STR and FORMAT functions ...
01:27
✨ SQL Journey - Mile 17✨ Ever wondered how to format your strings ...
The FORMAT function in SQL Server - extracting single or ...
Top answer 1 of 13
96
If you are using SQL Server 2012 and above, you can use FORMATMESSAGE. eg.
DECLARE @s NVARCHAR(50) = 'World';
DECLARE @d INT = 123;
SELECT FORMATMESSAGE('Hello %s, %d', @s, @d)
-- RETURNS 'Hello World, 123'
More examples from MSDN: FORMATMESSAGE
SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with leading zero %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
NOTES:
- Undocumented in 2012
- Limited to 2044 characters
- To escape the % sign, you need to double it.
- If you are logging errors in extended events, calling
FORMATMESSAGEcomes up as a (harmless) error
2 of 13
57
take a look at xp_sprintf. example below.
DECLARE @ret_string varchar (255)
EXEC xp_sprintf @ret_string OUTPUT,
'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
PRINT @ret_string
Result looks like this:
INSERT INTO table1 VALUES (1, 2)
Just found an issue with the max size (255 char limit) of the string with this so there is an alternative function you can use:
create function dbo.fnSprintf (@s varchar(MAX),
@params varchar(MAX), @separator char(1) = ',')
returns varchar(MAX)
as
begin
declare @p varchar(MAX)
declare @paramlen int
set @params = @params + @separator
set @paramlen = len(@params)
while not @params = ''
begin
set @p = left(@params+@separator, charindex(@separator, @params)-1)
set @s = STUFF(@s, charindex('%s', @s), 2, @p)
set @params = substring(@params, len(@p)+2, @paramlen)
end
return @s
end
To get the same result as above you call the function as follows:
print dbo.fnSprintf('INSERT INTO %s VALUES (%s, %s)', 'table1,1,2', default)
Dpriver
dpriver.com › pp › sqlformat.htm
Instant SQL Formatter & Beautifier Online
March 22, 2025 - Free online sql formatting tool, beautify sql code instantly for SQL Server, Oracle, DB2, MySQL, Sybase, Access and MDX
Databricks
docs.databricks.com › reference › sql language reference › functions › built-in functions › alphabetical list of built-in functions › format_string function
format_string function | Databricks on AWS
July 17, 2024 - Returns a formatted string from printf-style format strings.
Microsoft Power BI Community
community.powerbi.com › t5 › Desktop › How-to-Format-a-Text-String-as-SQL-Query › td-p › 2576159
Solved: How to Format a Text String as SQL Query - Microsoft Power BI Community
June 21, 2022 - In my opinion, I'd like to suggest your enter 'query editor' to add a custom column to invoke the T-SQL string formatting API that processes and store a formatted string into this custom column.
W3Schools
w3schools.com › sql › func_sqlserver_convert.asp
SQL Server CONVERT() Function
String Functions: Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Numeric Functions: Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Date Functions: Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Other Functions: CurrentUser Environ IsDate IsNull IsNumeric SQL Quick Ref
DataCamp
datacamp.com › tutorial › format-sql-function
FORMAT() SQL FUNCTION | DataCamp
October 12, 2022 - SELECT FORMAT(0112223333, ‘##-###-####') -- replace format with what is shown in the table below. Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.
W3Schools
w3schools.com › sql › sql_datatypes.asp
SQL Data Types for MySQL, SQL Server, and MS Access
String Functions: ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Date Functions: ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DA
Mimo
mimo.org › glossary › sql › format
SQL FORMAT() Function: Syntax, Usage, and Examples
The SQL FORMAT function allows you to convert numeric or date/time values into a formatted string using a specific pattern or culture. This function is especially useful in reports, dashboards, or any query where you want clean, user-friendly formatting of raw data.
W3Schools
w3schools.com › sql › sql_dates.asp
Date Functions in SQL Server and MySQL
String Functions: ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Date Functions: ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DA
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › string-functions-transact-sql
String Functions (Transact-SQL) - SQL Server
Scalar functions perform an operation on a string input value and return a string or numeric value, for example, ASCII (Transact-SQL). All built-in string functions except FORMAT are deterministic. This means they return the same value any time they are called with a specific set of input values.
PostgreSQL
postgresql.org › docs › current › functions-string.html
PostgreSQL: Documentation: 18: 9.4. String Functions and Operators
2 weeks ago - In addition, the format function does not require all function arguments to be used in the format string. For example: SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Result: Testing three, two, three · The %I and %L format specifiers are particularly useful for safely constructing dynamic SQL statements.
SQL Shack
sqlshack.com › a-comprehensive-guide-to-the-sql-format-function
A comprehensive guide to the SQL Format function
March 11, 2020 - Format: It is the required format in which we require the output. This parameter should contain a valid .NET format string in the NVARCHAR data type. We can refer to Format types in .NET for more details · Culture: It is an optional parameter. By default, SQL Server uses the current session ...
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › cast-and-convert-transact-sql
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
Reference for the CAST and CONVERT Transact-SQL functions. These functions convert expressions from one data type to another.
GeeksforGeeks
geeksforgeeks.org › sql › format-function-in-sql-server
SQL Server FORMAT() Function - GeeksforGeeks
June 7, 2024 - SQL Server FORMAT() function formats the specified value in the given format.
W3Schools
w3schools.com › sql › func_mysql_format.asp
MySQL FORMAT() Function
SQL Examples SQL Editor SQL Quiz ... Training ... The FORMAT() function formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places, then it returns the result as a string....
DB Vis
dbvis.com › thetable › how-to-format-sql-query-strings
How To Format SQL Query Strings
October 16, 2024 - As explored in our SQL formatter guide, there are numerous tools and approaches to formatting SQL queries. The most intuitive, logical, and time-saving solution is to format SQL query strings directly in your database client.
MSSQLTips
mssqltips.com › home › sql date format examples using convert function
SQL Date Format Examples using SQL CONVERT Function
September 26, 2025 - --SELECT a datetime column as a string formatted dd/mm/yyyy (4 digit year) SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 103) ExpectedDeliveryDateFormattedAsText FROM Purchasing.PurchaseOrders WHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted dd/mm/yy (2 digit year) SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 3) ExpectedDeliveryDateFormattedAsText FROM Purchasing.PurchaseOrders WHERE OrderDate < @Datetime; Once the query runs, review SQL CONVERT output as shown below.
W3Schools
w3schools.com › python › ref_string_format.asp
Python String format() Method
Remove List Duplicates Reverse ... Python Certificate Python Training ... The format() method formats the specified value(s) and insert them inside the string's placeholder....