Split values in a string and get them into varibles using xp_sscanf extended stored procedure

While I was writing some Hive queries for getting words from a lengthy string, I came across a useful extended stored procedure that allows us to split values in a string considering space as the separator. It has some limitations but it can be useful in some scenario.

Assume that you have a string value related to route calls like;

'947773333666 91333555444 1400 WE345TT094 CUS5629 CUS2792'

And you want to read it as;

CallerId, ReceiverId, Duration, Code, DirectInCustomer, DirectOutCustomer.

Then this extended stored procedure can be used for splitting it and storing them in variables. Have a look on the code;

view plainprint?

1. DECLARE @CallerId varchar(20)  
2. DECLARE @ReceiverId varchar(20)  
3. DECLARE @Duration varchar(20)  
4. DECLARE @Code varchar(20)  
5. DECLARE @DirectInCustomer varchar(20)  
6. DECLARE @DirectOutCustomer varchar(20)  
7.   
8. DECLARE @Record varchar(1000) = '947773333666 91333555444 1400 WE345TT094 CUS5629 CUS2792'  
9.   
10. exec master..xp_sscanf   
11.    @Record  
12.    , '%s %s %s %s %s %s'  
13.    , @CallerId OUTPUT  
14.    , @ReceiverId OUTPUT  
15.    , @Duration OUTPUT  
16.    , @Code OUTPUT  
17.    , @DirectInCustomer OUTPUT  
18.    , @DirectOutCustomer OUTPUT  
19.   
20. SELECT @CallerId As CallerId, @ReceiverId As ReceiverId, @Duration As Duration  
21.  , @Code As Code, @DirectInCustomer As DirectInCustomer, @DirectOutCustomer As DirectOutCustomer  

DECLARE @CallerId varchar(20)
DECLARE @ReceiverId varchar(20)
DECLARE @Duration varchar(20)
DECLARE @Code varchar(20)
DECLARE @DirectInCustomer varchar(20)
DECLARE @DirectOutCustomer varchar(20)

DECLARE @Record varchar(1000) = '947773333666 91333555444 1400 WE345TT094 CUS5629 CUS2792'

exec master..xp_sscanf 
   @Record
   , '%s %s %s %s %s %s'
   , @CallerId OUTPUT
   , @ReceiverId OUTPUT
   , @Duration OUTPUT
   , @Code OUTPUT
   , @DirectInCustomer OUTPUT
   , @DirectOutCustomer OUTPUT

SELECT @CallerId As CallerId, @ReceiverId As ReceiverId, @Duration As Duration
 , @Code As Code, @DirectInCustomer As DirectInCustomer, @DirectOutCustomer As DirectOutCustomer

This can be really useful when you have a large recordset with values like above and they need to be split. 

Read more details at: https://msdn.microsoft.com/en-us/library/ms181431.aspx