String.Format like functionality in T-SQL?

by

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 FORMATMESSAGE comes up as a (harmless) error

More Related Contents:

  1. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  2. Levenshtein distance in T-SQL
  3. How to get sp_executesql result into a variable?
  4. How can I use optional parameters in a T-SQL stored procedure?
  5. Select query to remove non-numeric characters
  6. T-SQL Decimal Division Accuracy
  7. How to split a single column values to multiple column values?
  8. Selecting a Record With MAX Value
  9. T-SQL Pivot? Possibility of creating table columns from row values
  10. What is a “batch”, and why is GO used?
  11. TSQL divide by zero encountered despite no columns containing 0
  12. T-SQL loop over query results
  13. How to Round a Time in T-SQL
  14. t-sql get all dates between 2 dates [duplicate]
  15. Return a value if no rows are found in Microsoft tSQL
  16. How to pivot table with T-SQL?
  17. Using Aliases in Where Clause or an Alternative Option?
  18. How do I remove extended ASCII characters from a string in T-SQL?
  19. Testing for inequality in T-SQL
  20. Can I use the MERGE statement in SQL Server 2005?
  21. SQL to find first non-numeric character in a string
  22. Replace a newline in TSQL
  23. T-SQL Split Word into characters
  24. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  25. T-SQL – function with default parameters
  26. How to use an Alias in a Calculation for Another Field
  27. Check if a varchar is a number (T-SQL)
  28. Using a cursor with dynamic SQL in a stored procedure
  29. How to parse string into date?
  30. Stored procedure hangs seemingly without explanation

Leave a Comment