Share via

SQL Server: Unusual String Functions

  • Article

One of the unusual string functions in SQL Server is formatmessage().

What is formatmessage() function? Where may we need to use this unusual function?

Using FORMATMESSAGE(), you can format strings using a printf()-like syntax. It takes a parameter specifying the ID of the message from the master..sysmessages table that you want to use, as well as a list of arguments to insert into the message. FORMATMESSAGE() works similarly to RAISERROR(), except that it doesn't return an error. Instead, it returns the resulting message as a string, which you may then do with as you please. Unfortunately, FORMATMESSAGE() is limited to messages that exist in sysmessages—you can't use it to format a plain character string.

With the help of formatmessage() function, we can provide awesome results for printing our result query.  Here's a technique for working around that.

First, we need to create a table and then need to run our query:

CREATE TABLE  #engagements
(Engagement varchar(30),
 EngagementStart smalldatetime,
 EngagementEnd smalldatetime)
INSERT #engagements VALUES('Gulf of Tonkin','19640802','19640804')
INSERT #engagements VALUES('Da Nang','19650301','19650331')
INSERT #engagements VALUES('Tet Offensive','19680131','19680930')
INSERT #engagements VALUES('Bombing of Cambodia','19690301','19700331')
INSERT #engagements VALUES('Invasion of Cambodia','19700401','19700430')
INSERT #engagements VALUES('Fall of Saigon','19750430','19750430') 
 
 
DECLARE @msg varchar(60), @msgid int, @Engagement_id varchar(10), @inprint int
SELECT @msgid=ISNULL(MAX(error)+1,999999) FROM  master..sysmessages WHERE error >50000
SELECT @Engagement_id=CAST(Engagement AS  varchar), @inprint=COUNT(*) FROM  #engagements GROUP  BY Engagement
 --Get the last one
BEGIN TRAN
EXEC sp_addmessage @msgid,1,'Show  Format Message: %s has %d titles in print'
SET @msg=FORMATMESSAGE(@msgid,@Engagement_id,@inprint)
ROLLBACK TRAN
SELECT @msg 
 
 
(No column  name)
Show  Format Message: Tet Offens has 1 titles in print

The results were successful. We got good result for printing from simple data.