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