SQL: String Format Function

Here’s a handy SQL function that allows to insert string(s) inside a larger string. This function is similar to String.Format() function in C#.

Typical Usage:

Have a string like below:

“%s is a. %s and %s does %s ”

with as many ” %s ” as required to dynamically insert values.

Insert desired string objects using the function.

SQL Function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DBO.FN_SPRINTF
(
@STRING VARCHAR(MAX),
@PARAMS VARCHAR(MAX),
@PARAM_SEPARATOR CHAR(1) = ','
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @P VARCHAR(MAX)
DECLARE @PARAM_LEN INT

SET @PARAMS = @PARAMS + @PARAM_SEPARATOR
SET @PARAM_LEN = LEN(@PARAMS)
WHILE NOT @PARAMS = ''
BEGIN
SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)
SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)
SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
END
RETURN @STRING

END

The function has three required parameters:

1. @STRING VARCHAR(MAX) : This is the main string to format
2. @PARAMS VARCHAR(MAX) : List of objects to insert
3. @PARAM_SEPARATOR CHAR(1)  : Identifier to parameter separator

Demo:

declare @test varchar(400)

select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','

print @test -- result: I am 1 and you are 0

select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator '#'

print @test -- result: I am 1 and you are 0