Function to provide String.Format-like functionality

String.Format is an extremely helpful tool in the C# world. Sadly T/SQL seems to lack the same functionality with the same ease of access.

The following script will allow you to generate strings much like String.Format does.

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

CREATE FUNCTION [dbo].[FormatString]
(
@Format NVARCHAR(4000) ,
@Parameters NVARCHAR(4000)
)
RETURNS NVARCHAR(MAX)
AS
/***************************************************
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
----------------------------------------------------------------------------
----------------------------------------------------------------------------
*******************************************/
BEGIN
DECLARE @Message NVARCHAR(400),
@Delimiter CHAR(1)
DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Parameter VARCHAR(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 ( Parameter )
SELECT
[ID] = SUBSTRING ( @Parameters, StartPos, CASE WHEN EndPos > 0 THEN EndPos - StartPos ELSE 4000 END )
FROM CTE
UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Parameter )
RETURN @Message
END
GO
GRANT EXECUTE,REFERENCES ON [dbo].[FormatString] TO [public]
GO

As noted in the script comments, usage is as follows

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

Source can be found here.

Subscribe

0 comments