SQL SERVER – How to use xp_sscanf in Real World Scenario?

I need your help. I recently came across extended stored procedure xp_sscanf. After reading a lot about it and searching online, I could not figure out how and where in real world, I will use this function.

Microsoft documentations suggest that this extended stored procedure reads data from the string into the argument locations specified by each format argument. I still do not get it. I know it is very similar to C function but again I am not sure where in the real world I will use this function. Here is the demonstration of how this function works.

Following example is an enhanced version of the example provided on MSDN.

DECLARE @valueb VARCHAR(20), @filename VARCHAR(20), @message VARCHAR(20)
EXEC xp_sscanf 'sync -b123 -fauthors10.tmp -rrandom', 'sync -b%s -f%s -r%s',
@valueb OUTPUT, @filename OUTPUT, @message OUTPUT
SELECT @valueb, @filename, @message

The above query will return us following result:

-------------------- -------------------- --------------------
123                  authors10.tmp        random

Here is the result set.

You can see how xp_sscanf retrieves the parameters from the string and returns as an output. However, I am still not sure where I will use this feature in real world scenarios. Any insight and or guidance will be helpful.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – How to use xp_sscanf in Real World Scenario?

  1. Hi,

    I think in current sql server version, we are not going to use this function. We can use PATINDEX and CHARINDEX for grab a particular value or string.
    Another point about this function that, This function only grab single string not complete string. So it’s completely limited.

    Only in one scenario we can use it. Like we have a table which contains some kind of configuration information in numeric or single value format. Check below table example:-

    — DROP TABLE TEST_sscanf
    CREATE TABLE TEST_sscanf (id int, StartEndTime Varchar(max))
    GO

    INSERT INTO TEST_sscanf values (1, ‘-a09.00 -b10.00 -c1.00hr’)
    INSERT INTO TEST_sscanf values (2, ‘-a09.10 -b10.20 -c1.10hr’)
    INSERT INTO TEST_sscanf values (3, ‘-a09.20 -b10.40 -c1.20hr’)
    GO

    SELECT * FROM TEST_sscanf

    Thanks
    Vinay Kumar

    • In Europe, a person can have multiple firtname. These two names are usually stored in the same column. xp_scanf can separate it.

      DECLARE @firstname VARCHAR(256)
      DECLARE @var1 VARCHAR(256)
      DECLARE @var2 VARCHAR(256)
      SET @firstname = ‘PHILIPPE GEORGES’
      exec master..xp_sscanf @firstname, ‘%s %s’, @var1 OUTPUT, @var2 OUTPUT
      SELECT @firstName, @var1, @var2

  2. Although I haven’t come across this function till now. But I can find a really nice use of it when I get to write any db tool or ORM for DB such as LinqToSql or Entity Framework, LLBGen, NHibernate.
    There it should help a lot when you need to generate classes as various entities.

  3. this funcation is same as substring ,PATINDEX and CHARINDEX to grab a particular value or string.
    this can be used in one scenario where we want to save data in one column along with some special char as start that denote start of string
    and retrive that data with diffrent column for user edit mode

    see following example

    DECLARE @FirstName VARCHAR(20), @Lastname VARCHAR(20), @Midname VARCHAR(20)
    EXEC xp_sscanf ‘sync *krishna -patil -S’, ‘sync *%s -%s -%s’,
    @FirstName OUTPUT, @Lastname OUTPUT, @Midname OUTPUT
    SELECT @FirstName, @Lastname , @Midname

  4. I see this function would be helpful if we are loading lot of external files in to sql which has different format (some comma delimited, some fixed length).

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