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.

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

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 (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters
Next Post
SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

Related Posts

7 Comments. Leave new

  • vinay kumar
    May 14, 2013 11:46 am


    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))

    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’)

    SELECT * FROM TEST_sscanf

    Vinay Kumar

  • Confusing …Out of my mind :-(

    • 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

  • Dharmindar Devsidas
    May 14, 2013 5:28 pm

    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.

  • krishna patil
    May 14, 2013 5:47 pm

    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

  • 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).

  • Satish Vellanki
    June 27, 2016 5:08 pm

    exec master..xp_sscanf @firstname, ‘%s %s’, @var1 OUTPUT, @var2 OUTPUT
    Here why r u using Master..


Leave a ReplyCancel reply

Exit mobile version