Is FORMATMESSAGE returns several string for one parameter value?

Question

FORMATMESSAGE function returns NULL when one parameter value using for several msg string.

DECLARE @myvar VARCHAR(10) = 100

SELECT FORMATMESSAGE ('First value %s, Second value %s' , @myvar) AS String

Expected result: First value 100, Second value 100

Actual result: First value 100, Second value null

Kristofer · Accepted Answer · 2019-01-21 12:14:25Z

4

You need to specify each parameter:

DECLARE @myvar VARCHAR(10) = 100
SELECT FORMATMESSAGE ('First value %s, Second value %s' , @myvar, @myvar) AS String

answered Jan 21, 2019 at 12:14

Kristofer

8739 silver badges17 bronze badges

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

3 Comments

Tom Kev Over a year ago

Hi, Kristofer Thank you! But what to do if I will have variable amount of "%s", for example, 70? It is improperly to repeat @myvar 70 times.

Kristofer Over a year ago

Unfortunately you cannot do that with FORMATMESSAGE without adding your variable 70 times. If you want to repeat the same value many times you can use REPLICATE: SELECT REPLICATE('repeat this', 20).

Tom Kev Over a year ago

I used REPLACE function - SELECT REPLACE ('First value %s, Second value %s' , '%s', @myvar) AS String

Collectives™ on Stack Overflow

Is FORMATMESSAGE returns several string for one parameter value?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related