xp_sscanf (Transact-SQL)

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

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • string
    Is the character string to read the argument values from.

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

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

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

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

Return Code Values

0 (success) or 1 (failure)

Result Sets

xp_sscanf returns the following message:

Command(s) completed successfully.

Permissions

Requires membership in the public role.

Examples

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