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:
- @STRING VARCHAR(MAX) : This is the main string to format
- @PARAMS VARCHAR(MAX) : List of objects to insert
- @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