SQL Server xp_sprintf and xp_sscanf

July 06, 2016

I learned about these two functions accidentally last year, and I wanted to try them both out to see what they do. A long time ago, I used to be a C programmer, so the concepts of the two functions are familiar to me (assuming they are similar to their C cousins).

xp_sprintf

This is kind of like the C function sprintf… except it isn’t. Firstly, it only supports the %s placeholder. This means that you can’t represent, or format a float, like you can in C. Secondly, it looks like it only works with strings of 255 characters or less. Here’s an example:

[code lang=“SQL”] DECLARE @myString varchar (255) EXEC xp_sprintf @myString OUTPUT, ‘There are %s fields in the table %s’, ‘3’, ‘TestTable’

SELECT @myString




[![sql1](http://pmichaels.net/wp-content/uploads/2016/07/sql1.jpg)](http://pmichaels.net/wp-content/uploads/2016/07/sql1.jpg)

Admittedly, that seems a bit pointless; but what if we do this:

[code lang="SQL"]

DECLARE @colCount INT
DECLARE @colCountStr varchar(255)
DECLARE @myString varchar(255)
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT @colCount = COUNT(COLUMN\_NAME) 
FROM INFORMATION\_SCHEMA.COLUMNS WHERE 
TABLE\_CATALOG = 'TestDB' AND TABLE\_SCHEMA = 'dbo'
AND TABLE\_NAME = @tableName

SELECT @colCountStr = CONVERT(varchar, @colCount)

EXEC xp\_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', @colCountStr, @tableName

SELECT @myString

sql2

It makes a bit more sense. However, there are a number of restrictions with the procedure. There is the 255 characters, you can only substitute strings and, because it’s a procedure, you can’t include it in a query.

A better way: FORMATMESSSAGE

FORMATMESSAGE is a function, but it supports all the standard placeholders:

[code lang=“SQL”]

DECLARE @colCount INT DECLARE @tableName varchar(255)

set @tableName = ‘Audit’

SELECT FORMATMESSAGE(‘There are %d fields in the table %s’, COUNT(COLUMN_NAME), @tableName) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = ‘TestDB’ AND TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = @tableName




[![sql2](http://pmichaels.net/wp-content/uploads/2016/07/sql2.jpg)](http://pmichaels.net/wp-content/uploads/2016/07/sql2.jpg)

**xp\_sscanf**

This looks like a far more useful function; as it allows parsing of a string.  Whether or not you believe you should be storing your data in a manner that requires parsing, one day, you will need to do it.  Consider this table:

[![scanf](http://pmichaels.net/wp-content/uploads/2016/07/scanf.png)](http://pmichaels.net/wp-content/uploads/2016/07/scanf.png)

So, I want to analyse this data; how about:

[code lang="SQL"]
select \*
from dbo.[Audit]

DECLARE @colour varchar(255)
DECLARE @text varchar(255)

SELECT @text = AuditText from dbo.[Audit]

EXEC xp\_sscanf @text, 'The sky is %s today', @colour OUTPUT

select @colour


sql3

The eagle eyed amongst you might notice a slight issue here - because it’s a procedure, it can’t be used inside a query. It’s also hampered by the same restrictions of 255 characters, etc… I couldn’t find an xp_scanf equivalent of FORMATMESSAGE, so I rolled my own:

[code lang=“SQL”] CREATE FUNCTION ScanFWrapper ( @inputText varchar(255), @formatText varchar(255) ) RETURNS varchar(255) AS BEGIN DECLARE @Result varchar(255)

EXEC xp\_sscanf @inputText, @formatText, @Result OUTPUT
	
RETURN @Result

END GO




Admittedly, it's not very generic, but you can call it like this:



SELECT dbo.ScanFWrapper(AuditText, ‘The sky is %s today’) FROM dbo.[Audit]




[![sql4](http://pmichaels.net/wp-content/uploads/2016/07/sql4.jpg)](http://pmichaels.net/wp-content/uploads/2016/07/sql4.jpg)

**Conclusion**

There are a number of xp\_\* methods, and they seem to be one-off procedures, so I'm probably being unfair on them in trying to compare them to their C equivalents.


Profile picture

A blog about one man's journey through code… and some pictures of the Peak District
Twitter

© Paul Michaels 2022