Home > SQL Server > SQL SERVER – Assign value to variables using xp_sscanf – System Stored Procedure

SQL SERVER – Assign value to variables using xp_sscanf – System Stored Procedure


xp_sscanf : This stored procedure is used to read single or multiple portion of a string and assign into the output string variables. This is similar to the function in the famous programming language C. In C language, scanf() function is used as an input function to read values from the input device to variables.

Usage

xp_sscanf  string, format, argument 1,…,argument n

  • input string, This string is the input reader string.
  • format, This is the format string value contains the format symbol %s to represent the string value at that position.
  • n, It represents the count of argument. we can set up to 50 output string variables (arguments).
  • OUTPUT is used next to a string output variable (arguments) as an output parameter indicator.

Example

1. In this example. I am going to read string values day, month and year from the getdate() output string value.

declare @dt as varchar(50) = getdate()

declare @day as varchar(10)
declare @month as varchar(10)
declare @year as varchar(10)

exec xp_sscanf @dt, ‘%s %s %s’, @month output, @day output, @year output

select @day ‘Day’, @month ‘Month’, @year ‘Year’

 Output

pic1This xp_sscanf is a useful stored procedure to derive a portion of a string that is also in  predefined location.

This stored procedure also remind me the FormatMessage function in SQL Server. The functionality is slightly similar to xp_sscanf stored procedure. But it is used for error message redefinition purpose.

I hope you have understood the use of xp_sscanf stored procedure. Your queries can be written in the comment section.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: