Splitting values in a string variable and inserting values as rows - I

Challenges are interesting and finding various ways to solve is a thrilling adventure.

One of the codes I had to write today was, splitting a string value received from an ASP.Net application and inserting them into a table. There are many ways of splitting a string values (or sometime, converting columns into rows) but this was bit different. The values I receive from the application are something like;

"SQL Business_Intelligence Azure"

"Personal Fun_time Crazy_Stuff"

Now how can I convert them into individual values (as rows) and insert them into a table?

There is a useful extended stored procedure that allows us to split values in a string considering space as the separator. It is xp_sscanf. It has some limitations but it can be useful in some scenario.

You can read more on it at: https://msdn.microsoft.com/en-us/library/ms181431.aspx

Here is the function I wrote for splitting values;

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. CREATE OR ALTER FUNCTION dbo.SplitString (@String nvarchar (4000))  
5. RETURNS @Tags TABLE  
6. (  
7.  Tag nvarchar(200)  
8. )  
9. AS  
10. BEGIN  
11.   
12.  DECLARE @Tag1 nvarchar(200)  
13.    , @Tag2 nvarchar(200)  
14.    , @Tag3 nvarchar(200)  
15.    , @Tag4 nvarchar(200)  
16.    , @Tag5 nvarchar(200)  
17.    , @Tag6 nvarchar(200)  
18.    , @Tag7 nvarchar(200)  
19.    , @Tag8 nvarchar(200)  
20.    , @Tag9 nvarchar(200)  
21.    , @Tag10 nvarchar(200)  
22.   
23.  EXEC xp_sscanf @String, '%s %s %s %s %s %s %s %s %s %s',     
24.   @Tag1 OUTPUT, @Tag2 OUTPUT, @Tag3 OUTPUT, @Tag4 OUTPUT, @Tag5 OUTPUT  
25.   , @Tag6 OUTPUT, @Tag7 OUTPUT, @Tag8 OUTPUT, @Tag9 OUTPUT, @Tag10 OUTPUT;    
26.   
27.  INSERT INTO @Tags  
28.   (Tag)  
29.  SELECT T.*  
30.  FROM (  
31.   SELECT REPLACE(@Tag1, '_', ' ') AS NewTag  
32.   UNION ALL  
33.   SELECT REPLACE(@Tag2, '_', ' ')  
34.   UNION ALL  
35.   SELECT REPLACE(@Tag3, '_', ' ')  
36.   UNION ALL  
37.   SELECT REPLACE(@Tag4, '_', ' ')  
38.   UNION ALL  
39.   SELECT REPLACE(@Tag5, '_', ' ')  
40.   UNION ALL  
41.   SELECT REPLACE(@Tag6, '_', ' ')  
42.   UNION ALL  
43.   SELECT REPLACE(@Tag7, '_', ' ')  
44.   UNION ALL  
45.   SELECT REPLACE(@Tag8, '_', ' ')  
46.   UNION ALL  
47.   SELECT REPLACE(@Tag9, '_', ' ')  
48.   UNION ALL  
49.   SELECT REPLACE(@Tag10, '_', ' ')) AS T  
50.  WHERE T.NewTag IS NOT NULL;  
51.   
52.  RETURN;  
53. END  
54. GO  

USE tempdb;
GO

CREATE OR ALTER FUNCTION dbo.SplitString (@String nvarchar (4000))
RETURNS @Tags TABLE
(
 Tag nvarchar(200)
)
AS
BEGIN

 DECLARE @Tag1 nvarchar(200)
   , @Tag2 nvarchar(200)
   , @Tag3 nvarchar(200)
   , @Tag4 nvarchar(200)
   , @Tag5 nvarchar(200)
   , @Tag6 nvarchar(200)
   , @Tag7 nvarchar(200)
   , @Tag8 nvarchar(200)
   , @Tag9 nvarchar(200)
   , @Tag10 nvarchar(200)

 EXEC xp_sscanf @String, '%s %s %s %s %s %s %s %s %s %s',   
  @Tag1 OUTPUT, @Tag2 OUTPUT, @Tag3 OUTPUT, @Tag4 OUTPUT, @Tag5 OUTPUT
  , @Tag6 OUTPUT, @Tag7 OUTPUT, @Tag8 OUTPUT, @Tag9 OUTPUT, @Tag10 OUTPUT;  

 INSERT INTO @Tags
  (Tag)
 SELECT T.*
 FROM (
  SELECT REPLACE(@Tag1, '_', ' ') AS NewTag
  UNION ALL
  SELECT REPLACE(@Tag2, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag3, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag4, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag5, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag6, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag7, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag8, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag9, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag10, '_', ' ')) AS T
 WHERE T.NewTag IS NOT NULL;

 RETURN;
END
GO

And this is how I can use it;

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. IF OBJECT_ID('dbo.Tags') IS NOT NULL  
5.  DROP TABLE dbo.Tags;  
6.   
7. CREATE TABLE dbo.Tags  
8. (  
9.  TagId int identity(1,1) primary key  
10.  , Tag nvarchar(200)  
11. );  
12. GO  
13.   
14. DECLARE @String nvarchar(4000) = 'SQL Business_Intelligence Azure';  
15.   
16. INSERT INTO dbo.Tags  
17.  (Tag)  
18. SELECT Tag  
19. FROM dbo.SplitString (@String);  
20.   
21. SELECT * FROM dbo.Tags;  

USE tempdb;
GO

IF OBJECT_ID('dbo.Tags') IS NOT NULL
 DROP TABLE dbo.Tags;

CREATE TABLE dbo.Tags
(
 TagId int identity(1,1) primary key
 , Tag nvarchar(200)
);
GO

DECLARE @String nvarchar(4000) = 'SQL Business_Intelligence Azure';

INSERT INTO dbo.Tags
 (Tag)
SELECT Tag
FROM dbo.SplitString (@String);

SELECT * FROM dbo.Tags;

As you see with the second script, I can simply pass the string received, get them split and insert to the required table.

Is this working in Azure SQL Database?
Unfortunately, it does not work in Azure SQL Database as Azure SQL does not support Extended Stored Procedure. However, good news is, SQL Server 2016 has a new function that can be used with both SQL Server 2016 and Azure SQL Database. Here is a sample code for it.