Tuesday, December 21, 2010

String.Format in T-SQL



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