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 (https://blog.sqlauthority.com)
7 Comments. Leave new
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
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
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.
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).
exec master..xp_sscanf @firstname, ‘%s %s’, @var1 OUTPUT, @var2 OUTPUT
Here why r u using Master..