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
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
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
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:
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
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]
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.