string interpolation in sql server

weixin_33966365

于 2018-11-14 10:46:00 发布

阅读量85

点赞数

文章标签：数据库

https://sqlserver.dev129.com/2018/01/29/string-interpolation-in-t-sql/

Most programming languages have the ability to interpolate formatted strings. For example, in C#

String input = String.Format("oh hi {0}", "mike");

This is also possible in T-SQL through the use of two functions.
In SQL Server 2008 the function xp_sprintf was introduced

Also, in SQL Server 2012 the FORMATMESSAGE function was added.

两个单引号，会转义为一个单引号

SELECT   FORMATMESSAGE(
             'exec pi_NoUseWebpartReplacement @Id = %s,
@Category = ''%s'',
@WebpartCode = ''%s'',
@WebpartName = ''%s'',
@WebpartFile = ''%s'',
@Replacement = ''%s''' ,
             CONVERT(NVARCHAR(255), Id),
             Category ,
             WebpartCode ,
             WebpartName ,
             WebpartFile ,
             Replacement)
FROM     dbo.LISA_NoUseWebpartReplacement
ORDER BY Id;