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:

DECLARE @myString varchar (255)
EXEC xp\_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', '3', 'TestTable'

SELECT @myString

sql1

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


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:


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

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

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

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:

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

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 2024