Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

xp_sscanf (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Reads data from the string into the argument locations specified by each format argument.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

xp_sscanf { string OUTPUT , format } [ ,argument [ ,...n ] ] 


Is the character string to read the argument values from.


When specified, puts the value of argument in the output parameter.


Is a formatted character string similar to what is supported by the C-language sscanf function. Currently, only the %s format argument is supported.


Is a varchar variable set to the value of the corresponding format argument.


Is a placeholder that indicates that a maximum of 50 arguments can be specified.

0 (success) or 1 (failure)

xp_sscanf returns the following message:

Command(s) completed successfully.

Requires membership in the public role.

The following example uses xp_sscanf to extract two values from a source string based on their positions in the format of the source string.

DECLARE @filename varchar (20), @message varchar (20);
EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s', 
  @filename OUTPUT, @message OUTPUT;
SELECT @filename, @message;

Here is the result set.

-------------------- -------------------- 
products10.tmp        random

Community Additions

© 2015 Microsoft