TSQL String Format Characters

Question

TSQL String Format Characters

shew

SSChampion

Points: 11389
More actions
August 19, 2011 at 8:49 am

#390228

I've searched for TSQL string formatting information, and I came up empty...
For example, I ran across the following working code on a recent web search:
RAISERROR (N'This is message %s %d.', -- Message text.
10, -- Severity,
1, -- State,
N'number', -- First argument.
5); -- Second argument.
The "%s %d" part of the code appears to be some sort of format string. (Presumably, "%s" is for strings.) Can anyone tell me what this feature is called or point me to some string formatting documentation for TSQL?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Neal Sivley SSCommitted Points: 1766 More actions · Answer 1

The %S and %d are variables. T-sql doesn’t have a format string like.Net does. You could create a CLR user defined function to implement the .NET format string but I don’t know that I would do it.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

See if this expalantion helps.

All the explanation are in the comments.

Declare @month varchar(10) = 'NOvember' ,

@date int = 24

--== Can't concatenate variable in the Message section of the RAISERROR statement

raiserror('Puss in boots releases on ' + @month + cast(@date as varchar) , 10 , 1 , )

--== If a code returns the same error message but only a value in it varies

-- then we can make use of %s and %d

-- it also provides an advantage of not having to CAST/CONVERT interger based

-- data types in the message text

raiserror('"Puss in Boots" releases on %s %d' , 10 , 1 ,@month,@date )

set @month= 'July'

set @date = 12

raiserror('"Dark Knight Returns II" releases on %s %d' , 10 , 1 ,@month,@date )

--== We can make use of another variable to concatenate beforehand

-- this method involves CAST/CONVERT everytime

Declare @MessageText VARCHAR(50) = ''

set @month= 'November'

set @date = 12

SET @MessageText = '"Puss in Boots" releases on ' + @month + ' ' + CAST ( @date as VARCHAR(10))

raiserror(@MessageText , 10 , 1 )

set @month= 'July'

set @date = 12

SET @MessageText = '"Dark Knight Returns II" releases on ' + @month + ' ' + CAST ( @date as VARCHAR(10))

raiserror(@MessageText , 10 , 1 )

Now , as per the comments, you would see how using %s and %d makes the messaage text less complex.

Hope that helps

{Edit : Removed comments from a statemetn}

shew SSChampion Points: 11389 More actions · Answer 3

stewartc-708166 (8/19/2011)
You will find a little more detail here.

Thanks! That is exactly what I was looking for.

shew SSChampion Points: 11389 More actions · Answer 4

ColdCoffee (8/19/2011)
See if this expalantion helps.

Nice! Thank you.

TSQL String Format Characters

Cookies on SQLServerCentral