String.Format like functionality in T-SQL?

Question

Hi,

I'm looking for a built-in function/extended function in T-SQL for string manipulation similar to the String.Format method in .NET.

Answer 1

Raw t-sql is limited to CHARINDEX(), PATINDEX(), REPLACE(), and SUBSTRING() for string manipulation. But with sql server 2005 and later you can set up user defined functions that run in .Net, which means setting up a string.format() UDF shouldn't be too tough.

Answer 2

SQL Server is for retrieving data. Do your formatting client-side, in whatever language you're using on that side.

Answer 3

Not exactly, but I would check out some of the articles on string handling (amongst other things) by "Phil Factor" (geddit?) on Simple Talk.

Answer 4

There is a way, but it has it's 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's an article about it: http://msdn.microsoft.com/en-us/library/ms186788.aspx

It's kind of a shame there isn't an easier way to do this, because there are times when you want to format a string/varchar in the database. Hopefully you are only looking to format a string in a standard way and can use the sys.messages table.

Coincidentally, you could also use the raiseerror() function with a very low severity, the documentation for raiseerror even mentions doing this, but the results are only printed. So you wouldn't be able to do anything with the resulting value (from what I understand).

Good luck!

Answer 5

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 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)