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
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)
Videos
13:40
Prettify Your SQL Outputs with the FORMAT Function - YouTube
10:55
Converting numbers to strings in SQL Server: STR and FORMAT functions ...
Working With SQL Server Dates | Format Function, Culture ...
06:05
The FORMAT function in SQL Server - extracting single or multiple ...
05:23
SQL FORMAT() Function - YouTube
Database Guide
database.guide › what-is-a-format-string-in-sql-server
What is a Format String in SQL Server?
June 29, 2019 - In SQL Server, the FORMAT() function enables you to format date/time and number values as a formatted string by passing in a “format string” as the second argument (the first argument is the value that’s being formatted).
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › format-transact-sql
FORMAT (Transact-SQL) - SQL Server | Microsoft Learn
Remoting a function that requires the CLR, could cause an error on the remote server. FORMAT relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon, or period must be ...
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 ...
Andrewvillazon
andrewvillazon.com › placeholder-text-string-interpolation-t-sql
Placeholder Text (String Interpolation) in T-SQL
January 23, 2021 - Looking closer at this result set, you're likely to see error messages you regularly encounter in SQL Server. You can populate the message with values by calling FORMATMESSAGE with a message_id from sys.messages. You'll only be able to call messages with an id above 13000, a limitation of FORMATMESSAGE—a message_id 13000 or less results in NULL. SELECT FORMATMESSAGE(21821, 'Something', 'Some other thing'); Specify one and only one of the parameters - Something or Some other thing.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › formatmessage-transact-sql
FORMATMESSAGE (Transact-SQL) - SQL Server | Microsoft Learn
@msg_variable Applies to: SQL Server ( SQL Server 2016 (13.x) through current version). Is an nvarchar or varchar variable that contains a string complying with the criteria for msg_string above. param_value Is a parameter value for use in the message. Can be more than one parameter value. The values must be specified in the order in which the placeholder variables appear in the message. The maximum number of values is 20. ... Like the RAISERROR statement, FORMATMESSAGE edits the message by substituting the supplied parameter values for placeholder variables in the message.
Database Guide
database.guide › how-the-format-function-works-in-sql-server-t-sql
How the FORMAT() Function Works in SQL Server (T-SQL)
When using FORMAT, colons and periods must be escaped (this adheres to the the CLR formatting rules). Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with a backslash when an input value (first parameter) is of the time data type.
GeeksforGeeks
geeksforgeeks.org › sql › format-function-in-sql-server
SQL Server FORMAT() Function - GeeksforGeeks
June 7, 2024 - The FORMAT function in SQL Server is used to format date/time and number values with locale-aware formatting. The FORMAT function achieves various formatting requirements, showing dates in specific formats or formatting numeric values. ... Value: It is the value to do formatting. It should be in support of the data type format. Format: It is the required format in which we require the output. Culture: It is an optional parameter.
Rip Tutorial
riptutorial.com › format
Microsoft SQL Server Tutorial => Format
October 4, 2021 - DECLARE @Number AS DECIMAL(10,2) = 454545.389 SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39' FORMAT( @Number, 'N','en-IN') AS 'Number Format in INDIA', -- Returns '4,54,545.39' FORMAT( @Number, '#.0') AS 'With 1 Decimal', -- Returns '454545.4' FORMAT( @Number, '#.00') AS 'With 2 Decimal', -- Returns '454545.39' FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39' FORMAT( @Number, '##.00') AS 'Without Comma and 2 Decimal', -- Returns '454545.39' FORMAT( @Number, '000000000') AS 'Left-padded to nine digits' -- Returns '0004545
Sqlhints
sqlhints.com › 2013 › 06 › 23 › format-string-function-in-sql-server-2012
FORMAT STRING FUNCTION IN SQL SERVER 2012 | SqlHints.com
November 21, 2025 - DECLARE @date DATETIME = GETDATE() SELECT FORMAT ( @date, 'dd', 'en-US' ) AS 'US', FORMAT ( @date, 'ddd', 'en-US' ) AS 'US', FORMAT ( @date, 'dddd', 'en-US' ) AS 'US', FORMAT ( @date, 'dddd', 'kn-IN' ) AS 'Kannada', FORMAT ( @date, 'dddd', 'hi-IN' ) AS 'Hindi' SELECT FORMAT ( @date, 'M', 'en-US' ) AS 'US', FORMAT ( @date, 'MM', 'en-US' ) AS 'US', FORMAT ( @date, 'MMM', 'en-US' ) AS 'US', FORMAT ( @date, 'MMMM', 'en-US' ) AS 'US', FORMAT ( @date, 'MMMM', 'kn-IN' ) AS 'Kannada', FORMAT ( @date, 'MMMM', 'hi-IN' ) AS 'Hindi' SELECT FORMAT ( @date, 'y', 'en-US' ) AS 'US', FORMAT ( @date, 'y', 'kn-I
Database Journal
databasejournal.com › home › ms sql
The Format() Function in SQL Server 2012 | Database Journal
October 31, 2025 - The function FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where you pass the date value or numeric value. The second parameter is the.NET Framework format string.
DataCamp
datacamp.com › tutorial › format-sql-function
FORMAT() SQL FUNCTION | DataCamp
October 12, 2022 - We can format dates into different languages with the culture parameter. DECLARE @d DATE = CAST('2023-02-01' AS DATE); SELECT FORMAT(@d, format, culture) -- replace format with what is shown in the table below. We can also conveniently format numeric values into currencies. SELECT FORMAT(amount, 'c', culture) -- replace format with what is shown in the table below. FORMAT() works in SQL Server (starting with 2012), Azure SQL Database.
W3Schools
w3schools.com › sql › func_sqlserver_convert.asp
SQL Server CONVERT() Function
June 7, 2024 - 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
If you're migrating logic originally written in SQL code, be mindful that FORMAT increases string operations within queries. You can use FORMAT inside SELECT, CTE, or subqueries: ... WITH monthly_sales AS ( SELECT region, FORMAT(SUM(sales), 'C', 'en-US') AS formatted_sales FROM sales GROUP BY region ) SELECT * FROM monthly_sales; This prepares readable totals per region. When building JSON with SQL Server’s FOR JSON clause, FORMAT makes the output cleaner:
Top answer 1 of 2
1
That's quite a lot of concatenation but it will work fine if the string length is 11:
DECLARE @a varchar(11)
SET @a='40010510120'
SELECT '|' + SUBSTRING(@a, 1, 1)
+ '|' + SUBSTRING(@a, 2, 1)
+ '|' + SUBSTRING(@a, 3, 1)
+ '|' + SUBSTRING(@a, 4, 1)
+ '|-'
+ '|' + SUBSTRING(@a, 5, 1)
+ '|' + SUBSTRING(@a, 6, 1)
+ '|' + SUBSTRING(@a, 7, 1)
+ '|-'
+ '|' + SUBSTRING(@a, 8, 1)
+ '|' + SUBSTRING(@a, 9, 1)
+ '|-'
+ '|' + SUBSTRING(@a, 10, 1)
+ '|' + SUBSTRING(@a, 11, 1)
+ '|'
You can turn it into a function:
CREATE FUNCTION dbo.CustomFormat(@a NVARCHAR(11))
RETURNS varchar(29)
AS
BEGIN
RETURN '|' + SUBSTRING(@a, 1, 1)
+ '|' + SUBSTRING(@a, 2, 1)
+ '|' + SUBSTRING(@a, 3, 1)
+ '|' + SUBSTRING(@a, 4, 1)
+ '|-'
+ '|' + SUBSTRING(@a, 5, 1)
+ '|' + SUBSTRING(@a, 6, 1)
+ '|' + SUBSTRING(@a, 7, 1)
+ '|-'
+ '|' + SUBSTRING(@a, 8, 1)
+ '|' + SUBSTRING(@a, 9, 1)
+ '|-'
+ '|' + SUBSTRING(@a, 10, 1)
+ '|' + SUBSTRING(@a, 11, 1)
+ '|'
END
SELECT dbo.CustomFormat('40010510120')
2 of 2
1
Try this...
DECLARE @a varchar(11)
SET @a='40010510120'
Declare @i as int
set @i=1
declare @output as varchar(29)
set @output=''
while @i<=LEN(@a)
begin
if @i=5 or @i=8 or @i=10
set @output= @output + '|-|' + SUBSTRING(@a,@i,1)
else if @i=LEN(@a)
set @output= @output + '|' + SUBSTRING(@a,@i,1) + '|'
else
set @output= @output + '|' + SUBSTRING(@a,@i,1)
set @i=@i+1
end
select @a
select @output
Tutorialspoint
tutorialspoint.com › home › sql › sql string functions: format
SQL String Functions: FORMAT
November 18, 2025 - SELECT FORMAT(@CUR_DATE, 'MM/dd/yyyy', 'EN-US') AS 'CURRENT_DATE'; The above SQL query produces the following output − · +---------------+ | CURRENT_DATE | +---------------+ | 02/20/2022 | +---------------+ The following SELECT query converts a string into a custom string format −
SQLGeeksPro
sqlgeekspro.com › home › sql server format function quick guide
SQL Server FORMAT Function Quick Guide » SQLGeeksPro
July 19, 2025 - Not to mention, it should be one of the valid data types. Format: Specify the format in which you require the output. (for example, "MM/DD/YYYY"). It does not support Composite formatting.
DB Vis
dbvis.com › thetable › how-to-format-sql-query-strings
How To Format SQL Query Strings
September 26, 2025 - Using FORMAT(): SQL Server function to format dates (e.g., FORMAT(date, 'yyyy-MM-dd')). The format of a SQL query string significantly impacts its readability.
Database Guide
database.guide › standard-numeric-format-strings-supported-by-format-in-sql-server
Standard Numeric Format Strings Supported by FORMAT() in SQL Server
September 3, 2024 - This article provides a reference for the standard numeric format specifiers that can be used when formatting numbers using the FORMAT() function in SQL Server.