String.Format 类似 T-SQL 中的功能？

Question

提问by

I'm looking for a built-in function/extended function in T-SQL for string manipulation similar to the String.Formatmethod in .NET.

我正在 T-SQL 中寻找一个内置函数/扩展函数，用于类似于String.Format.NET 中的方法的字符串操作。

Answer 1

回答by g2server

If you are using SQL Server 2012 and above, you can use FORMATMESSAGE. eg.

如果您使用的是 SQL Server 2012 及更高版本，则可以使用FORMATMESSAGE. 例如。

DECLARE @s NVARCHAR(50) = 'World';
DECLARE @d INT = 123;
SELECT FORMATMESSAGE('Hello %s, %d', @s, @d)
-- RETURNS 'Hello World, 123'

More examples from MSDN: FORMATMESSAGE

更多来自 MSDN 的例子：FORMATMESSAGE

SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with leading zero %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');

NOTES:

笔记：

Undocumented in 2012
Limited to 2044 characters
To escape the % sign, you need to double it.
If you are logging errors in extended events, calling FORMATMESSAGEcomes up as a (harmless) error

2012 年无证
限制为 2044 个字符
要转义 % 符号，您需要将其加倍。
如果您在扩展事件中记录错误，调用FORMATMESSAGE会作为（无害的）错误出现

Answer 2

回答by Josh

take a look at xp_sprintf. example below.

看看xp_sprintf。下面的例子。

DECLARE @ret_string varchar (255)
EXEC xp_sprintf @ret_string OUTPUT, 
    'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
PRINT @ret_string

Result looks like this:

结果如下所示：

INSERT INTO table1 VALUES (1, 2)

Just found an issue with the max size (255 char limit) of the string with this so there is an alternative functionyou can use:

刚刚发现字符串的最大大小（255 个字符限制）存在问题，因此您可以使用一个替代函数：

create function dbo.fnSprintf (@s varchar(MAX), 
                @params varchar(MAX), @separator char(1) = ',')
returns varchar(MAX)
as
begin
declare @p varchar(MAX)
declare @paramlen int

set @params = @params + @separator
set @paramlen = len(@params)
while not @params = ''
begin
    set @p = left(@params+@separator, charindex(@separator, @params)-1)
    set @s = STUFF(@s, charindex('%s', @s), 2, @p)
    set @params = substring(@params, len(@p)+2, @paramlen)
end
return @s
end

To get the same result as above you call the function as follows:

要获得与上述相同的结果，请按如下方式调用该函数：

print dbo.fnSprintf('INSERT INTO %s VALUES (%s, %s)', 'table1,1,2', default)

Answer 3

回答by Karthik D V

I have created a user defined function to mimic the string.format functionality. You can use it.

我创建了一个用户定义的函数来模拟 string.format 功能。你可以使用它。

stringformat-in-sql

sql 中的字符串格式

Answer 4

回答by jj.

There is a way, but it has its limitations. You can use the FORMATMESSAGE()function. It allows you to format a string using formatting similar to the printf()function in C.

有一种方法，但它有其局限性。您可以使用该FORMATMESSAGE()功能。它允许您使用类似于printf()C 中函数的格式来格式化字符串。

However, the biggest limitation is that it will only work with messages in the sys.messages table. Here's an article about it: microsoft_library_ms186788

但是，最大的限制是它只能处理 sys.messages 表中的消息。这是一篇关于它的文章：microsoft_library_ms186788

It's kind of a shame there isn't an easier way to do this, because there are times when you want to format a string/varchar in the database. Hopefully you are only looking to format a string in a standard way and can use the sys.messagestable.

有点遗憾，没有更简单的方法可以做到这一点，因为有时您想在数据库中格式化字符串/varchar。希望您只想以标准方式格式化字符串并且可以使用该sys.messages表。

Coincidentally, you could also use the RAISERROR()function with a very low severity, the documentation for raiseerror even mentions doing this, but the results are only printed. So you wouldn't be able to do anything with the resulting value (from what I understand).

巧合的是，您也可以使用RAISERROR()严重性非常低的函数，raiseerror 的文档甚至提到这样做，但结果只是打印出来。所以你将无法对结果值做任何事情（据我所知）。

Good luck!

祝你好运！

Answer 5

回答by Joel Coehoorn

Raw t-sql is limited to CHARINDEX(), PATINDEX(), REPLACE(), and SUBSTRING() for string manipulation. But with sql server 2005 and later you can set up user defined functions that run in .Net, which means setting up a string.format() UDF shouldn't be too tough.

原始 t-sql 仅限于用于字符串操作的 CHARINDEX()、PATINDEX()、REPLACE() 和 SUBSTRING()。但是使用 sql server 2005 及更高版本，您可以设置在 .Net 中运行的用户定义函数，这意味着设置 string.format() UDF 应该不会太难。

Answer 6

回答by Vadim Loboda

One more idea.

还有一个想法。

Although this is not a universal solution - it is simple and works, at least for me :)

虽然这不是一个通用的解决方案 - 它很简单而且有效，至少对我来说:)

For one placeholder {0}:

对于一个占位符 {0}：

create function dbo.Format1
(
    @String  nvarchar(4000),
    @Param0  sql_variant
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';

    return replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));    
end

For two placeholders {0} and {1}:

对于两个占位符 {0} 和 {1}：

create function dbo.Format2
(
    @String  nvarchar(4000),
    @Param0  sql_variant,
    @Param1  sql_variant
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';

    set @String = replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));
       return     replace(@String, N'{1}', cast(isnull(@Param1, @Null) as nvarchar(4000))); 
end

For three placeholders {0}, {1} and {2}:

对于三个占位符 {0}、{1} 和 {2}：

create function dbo.Format3
(
    @String  nvarchar(4000),
    @Param0  sql_variant,
    @Param1  sql_variant,
    @Param2  sql_variant
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';

    set @String = replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));
    set @String = replace(@String, N'{1}', cast(isnull(@Param1, @Null) as nvarchar(4000))); 
       return     replace(@String, N'{2}', cast(isnull(@Param2, @Null) as nvarchar(4000)));
end

and so on...

等等...

Such an approach allows us to use these functions in SELECT statement and with parameters of nvarchar, number, bit and datetime datatypes.

这种方法允许我们在 SELECT 语句中使用这些函数，并使用 nvarchar、number、bit 和 datetime 数据类型的参数。

For example:

例如：

declare @Param0 nvarchar(10) = N'IPSUM' ,
        @Param1 int          = 1234567  ,
        @Param2 datetime2(0) = getdate();

select dbo.Format3(N'Lorem {0} dolor, {1} elit at {2}', @Param0, @Param1, @Param2);

Answer 7

回答by SP007

I think there is small correction while calculating end position.

我认为在计算结束位置时会有小的修正。

Here is correct function

这是正确的功能

**>>**IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL
DROP FUNCTION [dbo].[FormatString]
GO
/***************************************************
Object Name : FormatString
Purpose : Returns the formatted string.
Original Author : Karthik D V http://stringformat-in-sql.blogspot.com/
Sample Call:
SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' )
*******************************************/
CREATE FUNCTION [dbo].[FormatString](
    @Format NVARCHAR(4000) ,
    @Parameters NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    --DECLARE @Format NVARCHAR(4000), @Parameters NVARCHAR(4000) select @format='{0}{1}', @Parameters='hello,world'
    DECLARE @Message NVARCHAR(400), @Delimiter CHAR(1)
    DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Parameter VARCHAR(1000) )
    Declare @startPos int, @endPos int
    SELECT @Message = @Format, @Delimiter = ','**>>**

    --handle first parameter
     set @endPos=CHARINDEX(@Delimiter,@Parameters)
    if (@endPos=0 and @Parameters is not null) --there is only one parameter
        insert into @ParamTable (Parameter) values(@Parameters)
    else begin
        insert into @ParamTable (Parameter) select substring(@Parameters,0,@endPos)
    end

    while @endPos>0
    Begin
        --insert a row for each parameter in the 
        set @startPos = @endPos + LEN(@Delimiter)
        set @endPos = CHARINDEX(@Delimiter,@Parameters, @startPos)
        if (@endPos>0)
            insert into @ParamTable (Parameter) 
                select substring(@Parameters,@startPos,@endPos - @startPos)
            else
                insert into @ParamTable (Parameter) 
                select substring(@Parameters,@startPos,4000)            
    End

    UPDATE @ParamTable SET @Message = 
        REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Parameter )
    RETURN @Message
END
Go
grant execute,references on dbo.formatString to public

Answer 8

回答by Tejasvi Hegde

Here is my version. Can be extended to accommodate more number of parameters and can extend formatting based on type. Currently only date and datetime types are formatted.

这是我的版本。可以扩展以容纳更多数量的参数，并且可以根据类型扩展格式。目前仅格式化日期和日期时间类型。

Example:

例子：

select dbo.FormatString('some string %s some int %s date %s','"abcd"',100,cast(getdate() as date),DEFAULT,DEFAULT)
select dbo.FormatString('some string %s some int %s date time %s','"abcd"',100,getdate(),DEFAULT,DEFAULT)

Output:

输出：

some string "abcd" some int 100 date 29-Apr-2017
some string "abcd" some int 100 date time 29-Apr-2017 19:40

Functions:

职能：

create function dbo.FormatValue(@param sql_variant)
returns nvarchar(100)
begin
/*
Tejasvi Hegde, 29-April-2017
Can extend formatting here.
*/
    declare @result nvarchar(100)

    if (SQL_VARIANT_PROPERTY(@param,'BaseType') in ('date'))
    begin
       select @result = REPLACE(CONVERT(CHAR(11), @param, 106), ' ', '-')
    end
    else  if (SQL_VARIANT_PROPERTY(@param,'BaseType') in ('datetime','datetime2'))
    begin
       select @result = REPLACE(CONVERT(CHAR(11), @param, 106), ' ', '-')+' '+CONVERT(VARCHAR(5),@param,108)
    end
    else
    begin
       select @result = cast(@param as nvarchar(100))
    end
    return @result

/*
BaseType:
bigint
binary
char
date
datetime
datetime2
datetimeoffset
decimal
float
int
money
nchar
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
time
tinyint
uniqueidentifier
varbinary
varchar
*/   

end;


create function dbo.FormatString(
    @format nvarchar(4000)
    ,@param1 sql_variant = null
    ,@param2 sql_variant = null
    ,@param3 sql_variant = null
    ,@param4 sql_variant = null
    ,@param5 sql_variant = null
    )
returns nvarchar(4000)
begin
/*
Tejasvi Hegde, 29-April-2017

select dbo.FormatString('some string value %s some int %s date %s','"abcd"',100,cast(getdate() as date),DEFAULT,DEFAULT)
select dbo.FormatString('some string value %s some int %s date time %s','"abcd"',100,getdate(),DEFAULT,DEFAULT)
*/

    declare @result nvarchar(4000)

    select @param1 = dbo.formatValue(@param1)
    ,@param2 = dbo.formatValue(@param2)
    ,@param3 = dbo.formatValue(@param3)
    ,@param4 = dbo.formatValue(@param4)
    ,@param5 = dbo.formatValue(@param5)

    select @param2 = cast(@param2 as nvarchar)
    EXEC xp_sprintf @result OUTPUT,@format , @param1, @param2, @param3, @param4, @param5

    return @result

end;

Answer 9

回答by jmoreno

At the moment this doesn't really exist (although you can of course write your own). There is an open connect bug for it: https://connect.microsoft.com/SQLServer/Feedback/Details/3130221, which as of this writing has just 1 vote.

目前这并不存在（尽管您当然可以编写自己的）。它有一个开放连接错误：https: //connect.microsoft.com/SQLServer/Feedback/Details/3130221，截至撰写本文时只有 1 票。

Answer 10

回答by Brijesh Kumar Tripathi

Actually there is no built in function similar to string.Format function of .NET is available in SQL server.

实际上并没有类似于string的内置函数。SQL server中提供了.NET的Format函数。

There is a function FORMATMESSAGE()in SQL server but it mimics to printf() function of C not string.Format function of .NET.

SQL Server 中有一个函数FORMATMESSAGE()但它模仿 C 的 printf() 函数而不是 .NET 的 string.Format 函数。

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result

String.Format 类似 T-SQL 中的功能？

提问by

回答by g2server

回答by Josh

回答by Karthik D V

回答by jj.

回答by Joel Coehoorn

回答by Vadim Loboda

回答by SP007

回答by Tejasvi Hegde

回答by jmoreno

回答by Brijesh Kumar Tripathi

相关推荐

最近更新

标签

String.Format 类似 T-SQL 中的功能？

提问by

回答by g2server

回答by Josh

回答by Karthik D V

回答by jj.

回答by Joel Coehoorn

回答by Vadim Loboda

回答by SP007

回答by Tejasvi Hegde

回答by jmoreno

回答by Brijesh Kumar Tripathi

相关推荐

如何将 mongodb 集合中的数据加载到 Pandas 的 DataFrame 中？

pandas Python3 和熊猫

pandas 使用循环绘制 n 个图表 Python

pandas “numpy.ndarray”对象不可调用错误

相关推荐

最近更新

标签