String.Format in T-SQL

Hi All,

I have been searching for the string.format equivalent functionality in sql server.
Unfortunately I could not find exact match though I got xp_sprintf.

xp_sprintf will do format only when you add your message to system messages via using sp_addmessage procedure.

So, I came up with a function which works almost very similar to the way we do in C#.

For Example : In C# we format message like this.

string msg = string.format("Hi{0}, Welcome to our site {1}. Thank you {0}", "Karthik D V", "Google Mania");

Now, in T-sql, with my new function you can write same thing like this! :

DECLARE @msg NVARCHAR(4000)

SET @msg = 'Hi {0}, Welcome to our site {1}. Thank you {0}'

SELECT @msg = dbo.FormatString(@msg, N'Karthik D V, Google Mania')

PRINT @msg

OUTPUT :

Hi Karthik D V, Welcome to our site Google Mania. Thank you Karthik D V

Source Code:

-------------------------------------------------------------------------------------------------------------

IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL

DROP FUNCTION [dbo].[FormatString]

GO

/***************************************************

Object Name : FormatString

Purpose : Returns the formatted string.

Author : Karthik D V

Created Date: 21-Dec-2010

Sample Call:

SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' )

Modification History:

----------------------------------------------------------------------------

Date Modified By Modification Details

----------------------------------------------------------------------------

----------------------------------------------------------------------------

*******************************************/

CREATE FUNCTION [dbo].[FormatString]

(

@Format NVARCHAR(4000) ,

@Parameters NVARCHAR(4000)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

DECLARE @Message NVARCHAR(400),

@Delimiter CHAR(1)

DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), ParamterVARCHAR(1000) )

SELECT @Message = @Format, @Delimiter = ','

;WITH CTE (StartPos, EndPos) AS

(

SELECT 1, CHARINDEX(@Delimiter, @Parameters)

UNION ALL

SELECT EndPos + (LEN(@Delimiter)), CHARINDEX(@Delimiter,@Parameters, EndPos + (LEN(@Delimiter)))

FROM CTE

WHERE EndPos > 0

)

INSERT INTO @ParamTable ( Paramter )

SELECT

[ID] = SUBSTRING ( @Parameters, StartPos, CASE WHENEndPos > 0 THEN EndPos - StartPos ELSE 4000 END )

FROM CTE

UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Paramter )

RETURN @Message

END

GO

GRANT EXECUTE,REFERENCES ON [dbo].[FormatString] TO [public]

GO