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.
Read more details at: https://msdn.microsoft.com/en-us/library/ms181431.aspx