Tuesday, December 29, 2015

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;

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. 


No comments: