Similar function in SQL Server to string.format in c#

Question

In C#, if I do:

string code = string.format("C{0:000}", 7);

The result will be: C007

Is there a function in SQL Server that does the same thing? Or do I have to create a function myself?

If you're looking for an exact match, a CLR UDF may be the way to go. — Joe Enos
– Joe Enos, Commented Oct 2, 2014 at 19:54
TIme to update. Not in SQL 2005, but 2014 has a format function EXTREMELY similar to C#. — TomTom
– TomTom, Commented Oct 2, 2014 at 20:16
SQL Server 2012 and newer have a FORMAT function ..... — marc_s
– marc_s, Commented Oct 2, 2014 at 20:22
possible duplicate of Is there a way to get dates with custom formats in SQL Server? — GSerg
– GSerg, Commented Oct 2, 2014 at 20:28

crucifery · Accepted Answer · 2017-02-22 12:09:28Z

6

Use FORMATMESSAGE() function.

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

https://msdn.microsoft.com/en-us/library/ms186788.aspx

answered Feb 22, 2017 at 12:09

crucifery

4885 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tab Alleman · Accepted Answer · 2014-10-02 20:03:59Z

3

In straight TSQL, there is nothing as powerful as the C# string.format. The CONVERT() function offers some limited formatting, but for anything it can't handle you'd have to write your own function. Google "SQL String functions" to see what tools you have in your SQL arsenal.

answered Oct 2, 2014 at 20:03

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

1 Comment

Aaron Bertrand Over a year ago

Or, don't perform this formatting in the database in the first place.

Michael G · Accepted Answer · 2014-10-02 20:24:14Z

0

I would use REPLICATE, combined with RIGHT

'C' + RIGHT(REPLICATE('0', <Padding Amount Here>) + CAST(7 AS NVARCHAR(10)), <Padding Amount Here>)

Usage:

'C' + RIGHT(REPLICATE('0', 10) + CAST(7 AS NVARCHAR(10)), 10)

Produces the follow:

C0000000007

edited Oct 2, 2014 at 20:24

answered Oct 2, 2014 at 20:15

Michael G

6,7752 gold badges45 silver badges60 bronze badges

1 Comment

Michael G Over a year ago

Added length + switched to NVARCHAR

MikeG · Accepted Answer · 2014-10-03 14:32:30Z

0

SELECT 'C' + RIGHT('00' + CONVERT(varchar(3),7),3)

edited Oct 3, 2014 at 14:32

answered Oct 2, 2014 at 20:15

MikeG

5451 gold badge5 silver badges13 bronze badges

Collectives™ on Stack Overflow

Similar function in SQL Server to string.format in c#

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related