String.Format how functionality in T-SQL?

Question

I am looking for a built-in function / advanced function in T-SQL for string processing similar to the String.Format method in .NET.

Answer 1

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 

The result is as follows:

 INSERT INTO table1 VALUES (1, 2) 

---

Just found a problem with the maximum size (255 char limit) of the string, so there is an alternative function

 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) 

Answer 2

If you are using SQL Server 2012 or later, the first argument to FORMATMESSAGE may be a string. eg.

 -- RETURNS Hello World, 123 DECLARE @s VARCHAR(50) = 'World'; DECLARE @d INT = 123; SELECT FORMATMESSAGE('Hello %s, %d', @s, @d) 

To exit the% sign, you need to double it.

Additional 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
• If you log errors in extended events, the FORMATMESSAGE call appears as a (harmless) error.

Answer 3

I created a custom function that mimics the functionality of string.format. You can use it.

stringformat-in-sql

Answer 4

Raw t-sql is limited to CHARINDEX (), PATINDEX (), REPLACE (), and SUBSTRING () to process strings. But with sql server 2005 and later, you can configure custom functions that run in .Net, which means setting the string.format () UDF should not be too hard.

Answer 5

There is a way, but it has its limitations. You can use the FORMATMESSAGE() function. It allows you to format a string using formatting similar to the printf() function in C.

However, the biggest limitation is that it will only work with messages in the sys.messages table. Here is an article about it: microsoft_library_ms186788

It's kind of embarrassing that there is no simpler way to do this, because there are times when you want to format the / varchar line in the database. Hope you only want to format the string in a standard way and can use the sys.messages table.

By the way, you can also use the RAISERROR() function with a very low degree of severity, the documentation for raiseerror even mentions this, but the results are only printed. Thus, you cannot do anything with the resulting value (from what I understand).

Good luck

Answer 6

I think there is a slight correction when calculating the final position.

Here is the correct function

 **>>**IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL DROP FUNCTION [dbo].[FormatString] GO /*************************************************** Object Name : FormatString Purpose : Returns the formatted string. Original Author : Karthik DV http://stringformat-in-sql.blogspot.com/ Sample Call: SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' ) *******************************************/ CREATE FUNCTION [dbo].[FormatString]( @Format NVARCHAR(4000) , @Parameters NVARCHAR(4000) ) RETURNS NVARCHAR(4000) AS BEGIN --DECLARE @Format NVARCHAR(4000), @Parameters NVARCHAR(4000) select @format='{0}{1}', @Parameters='hello,world' DECLARE @Message NVARCHAR(400), @Delimiter CHAR(1) DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Parameter VARCHAR(1000) ) Declare @startPos int, @endPos int SELECT @Message = @Format, @Delimiter = ','**>>** --handle first parameter set @endPos=CHARINDEX(@Delimiter,@Parameters) if (@endPos=0 and @Parameters is not null) --there is only one parameter insert into @ParamTable (Parameter) values(@Parameters) else begin insert into @ParamTable (Parameter) select substring(@Parameters,0,@endPos) end while @endPos>0 Begin --insert a row for each parameter in the set @startPos = @endPos + LEN(@Delimiter) set @endPos = CHARINDEX(@Delimiter,@Parameters, @startPos) if (@endPos>0) insert into @ParamTable (Parameter) select substring(@Parameters,@startPos,@endPos - @startPos) else insert into @ParamTable (Parameter) select substring(@Parameters,@startPos,4000) End UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Parameter ) RETURN @Message END Go grant execute,references on dbo.formatString to public 

Answer 7

Here is my version. It can be expanded for more parameters and can expand type-based formatting. Currently, only date and date types are formatted.

Example:

 select dbo.FormatString('some string %s some int %s date %s','"abcd"',100,cast(getdate() as date),DEFAULT,DEFAULT) select dbo.FormatString('some string %s some int %s date time %s','"abcd"',100,getdate(),DEFAULT,DEFAULT) 

Output:

 some string "abcd" some int 100 date 29-Apr-2017 some string "abcd" some int 100 date time 29-Apr-2017 19:40 

Functions:

 create function dbo.FormatValue(@param sql_variant) returns nvarchar(100) begin /* Tejasvi Hegde, 29-April-2017 Can extend formatting here. */ declare @result nvarchar(100) if (SQL_VARIANT_PROPERTY(@param,'BaseType') in ('date')) begin select @result = REPLACE(CONVERT(CHAR(11), @param, 106), ' ', '-') end else if (SQL_VARIANT_PROPERTY(@param,'BaseType') in ('datetime','datetime2')) begin select @result = REPLACE(CONVERT(CHAR(11), @param, 106), ' ', '-')+' '+CONVERT(VARCHAR(5),@param,108) end else begin select @result = cast(@param as nvarchar(100)) end return @result /* BaseType: bigint binary char date datetime datetime2 datetimeoffset decimal float int money nchar numeric nvarchar real smalldatetime smallint smallmoney time tinyint uniqueidentifier varbinary varchar */ end; create function dbo.FormatString( @format nvarchar(4000) ,@param1 sql_variant = null ,@param2 sql_variant = null ,@param3 sql_variant = null ,@param4 sql_variant = null ,@param5 sql_variant = null ) returns nvarchar(4000) begin /* Tejasvi Hegde, 29-April-2017 select dbo.FormatString('some string value %s some int %s date %s','"abcd"',100,cast(getdate() as date),DEFAULT,DEFAULT) select dbo.FormatString('some string value %s some int %s date time %s','"abcd"',100,getdate(),DEFAULT,DEFAULT) */ declare @result nvarchar(4000) select @param1 = dbo.formatValue(@param1) ,@param2 = dbo.formatValue(@param2) ,@param3 = dbo.formatValue(@param3) ,@param4 = dbo.formatValue(@param4) ,@param5 = dbo.formatValue(@param5) select @param2 = cast(@param2 as nvarchar) EXEC xp_sprintf @result OUTPUT,@format , @param1, @param2, @param3, @param4, @param5 return @result end; 

Answer 8

here is what i found with my experiments using the built-in

Function FORMATMESSAGE ()

 sp_addmessage @msgnum=50001,@severity=1,@msgText='Hello %s you are #%d',@replace='replace' SELECT FORMATMESSAGE(50001, 'Table1', 5) 

when you call sp_addmessage, your message template is stored in the master.dbo.sysmessages system table (checked on SQLServer 2000).

You have to control the addition and removal of template rows from the table yourself, which is inconvenient if all you really need is a quick message on the results screen.

The solution provided by Kathik DV looks interesting, but does not work with SQL Server 2000, so I changed it a little, and this version should work with all versions of SQL Server:

 IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL DROP FUNCTION [dbo].[FormatString] GO /*************************************************** Object Name : FormatString Purpose : Returns the formatted string. Original Author : Karthik DV http://stringformat-in-sql.blogspot.com/ Sample Call: SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' ) *******************************************/ CREATE FUNCTION [dbo].[FormatString]( @Format NVARCHAR(4000) , @Parameters NVARCHAR(4000) ) RETURNS NVARCHAR(4000) AS BEGIN --DECLARE @Format NVARCHAR(4000), @Parameters NVARCHAR(4000) select @format='{0}{1}', @Parameters='hello,world' DECLARE @Message NVARCHAR(400), @Delimiter CHAR(1) DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Parameter VARCHAR(1000) ) Declare @startPos int, @endPos int SELECT @Message = @Format, @Delimiter = ',' --handle first parameter set @endPos=CHARINDEX(@Delimiter,@Parameters) if (@endPos=0 and @Parameters is not null) --there is only one parameter insert into @ParamTable (Parameter) values(@Parameters) else begin insert into @ParamTable (Parameter) select substring(@Parameters,0,@endPos) end while @endPos>0 Begin --insert a row for each parameter in the set @startPos = @endPos + LEN(@Delimiter) set @endPos = CHARINDEX(@Delimiter,@Parameters, @startPos) if (@endPos>0) insert into @ParamTable (Parameter) select substring(@Parameters,@startPos,@endPos) else insert into @ParamTable (Parameter) select substring(@Parameters,@startPos,4000) End UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Parameter ) RETURN @Message END Go grant execute,references on dbo.formatString to public 

Using:

 print dbo.formatString('hello {0}... you are {1}','world,good') --result: hello world... you are good 

Answer 9

Another idea.

Although this is not a universal solution - it just works, at least for me :)

For one placeholder {0}:

 create function dbo.Format1 ( @String nvarchar(4000), @Param0 sql_variant ) returns nvarchar(4000) as begin declare @Null nvarchar(4) = N'NULL'; return replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000))); end 

For two placeholders {0} and {1}:

 create function dbo.Format2 ( @String nvarchar(4000), @Param0 sql_variant, @Param1 sql_variant ) returns nvarchar(4000) as begin declare @Null nvarchar(4) = N'NULL'; set @String = replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000))); return replace(@String, N'{1}', cast(isnull(@Param1, @Null) as nvarchar(4000))); end 

For the three placeholders {0}, {1} and {2}:

 create function dbo.Format3 ( @String nvarchar(4000), @Param0 sql_variant, @Param1 sql_variant, @Param2 sql_variant ) returns nvarchar(4000) as begin declare @Null nvarchar(4) = N'NULL'; set @String = replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000))); set @String = replace(@String, N'{1}', cast(isnull(@Param1, @Null) as nvarchar(4000))); return replace(@String, N'{2}', cast(isnull(@Param2, @Null) as nvarchar(4000))); end 

etc.

This approach allows you to use these functions in a SELECT statement and with nvarchar, number, bit, and datetime data type parameters.

For example:

 declare @Param0 nvarchar(10) = N'IPSUM' , @Param1 int = 1234567 , @Param2 datetime2(0) = getdate(); select dbo.Format3(N'Lorem {0} dolor, {1} elit at {2}', @Param0, @Param1, @Param2); 

Answer 10

This really does not exist at the moment (although, of course, you can write your own). There is an open connection error for him: https://connect.microsoft.com/SQLServer/Feedback/Details/3130221 , which at the time of this writing has only 1 vote.

Answer 11

Not really, but I would look at some of the articles on string processing (among other things) on Phil Factor (geddit?) On Simple Talk.

Answer 12

This is a bad approach. you have to work with a dll build that will do the same for you with better performance.