How to split a string in sql server

We have a requirement to capture the list of key words in the given list of statements. In the below example will see How to split a string in sql server

There is a table called “SearchQueries”. It’s been having a list of queries, now the requirement is to split each and every statement into individual words and needs to be stored in a table “KeyWords”.

 Now the requirement is to split and store keywords from each record as below along with the number of occurrences.

 Example:

KeyWord       Count

Describe           12

Change              2

Data                  10

Capture              1

Etc…….

Script to create table and populate with data:

USE udayarumilli;
GO
CREATE TABLE SearchQueries(ID INT IDENTITY,Query NVARCHAR(MAX));
GO
INSERT INTO SearchQueries(Query)
SELECT'How to design 2012 SSRS reports using SQL Server Data Tools'
UNION
SELECT'What are the new features added in SSRS 2012'
UNION
SELECT'Describe Change Data Capture for SSIS in SQL Server 2012'
UNION
SELECT'What are the new features added in SSIS 2012'
UNION
SELECT'SSIS 2012 Support for Variable Number of Columns in a Flat File'
UNION
SELECT'SSIS Package Format Changed and the Specs are Open Source New in SSIS 2012'
UNION
SELECT'What are the new features added in SSAS 2012';
GO

Create a table to hold the keywords:

CREATE TABLE KeyWords(ID INT IDENTITY,Word NVARCHAR(255));

Now create a stored procedure to split the given statement into individual words.

IF EXISTS(SELECT 1 FROM SYS.SYSOBJECTS WHERE TYPE='P'AND NAME='usp_split_string')
BEGIN
	DROP PROCEDURE usp_split_string;
END
GO
CREATE PROCEDURE usp_split_string(@Txt NVARCHAR(MAX))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @temp TABLE (KeyWord NVARCHAR(250));
DECLARE @data NVARCHAR(MAX),
		@delimiter NVARCHAR(10),
		@pos	INT,
		@start	INT,
		@len	INT,
		@end	INT;

SELECT	@data = @Txt,
		@delimiter=' ',
		@len=LEN('.'+@delimiter+'.')- 2,
		@end=LEN(@data)+ 1,
		@start=1,
		@pos=0;
WHILE (@pos < @end)
BEGIN
	SET @pos = CHARINDEX(@delimiter,@data,@start);
	IF (@pos= 0) SET @pos=@end;

	INSERT @temp (KeyWord)
	SELECT SUBSTRING(@data,@start,@pos-@start);
	SET @start=@pos+@len;
END
INSERT INTO KeyWords(word)
SELECT KeyWord FROM @temp;
END

The stored procedure splits the statement into individual words. For example if we input a statement “SQL Server 2012” to the stored procedure it splits the statement into words an stored into table “KeyWords”.

i.e

SQL

Server

2012

Using the same logic we will apply the logic to existing table “SearchQueries” and prepare a list of keywords from the table.

BEGIN
	DECLARE @Max INT,
			@i INT,
			@STMT VARCHAR(100),
			@Category VARCHAR(200);

	TRUNCATE TABLE KeyWords;
	SELECT @max = MAX(ID) FROM SearchQueries;
	SET @i= 1;
	WHILE(@i <= @Max)
	BEGIN
		SELECT	@stmt = Query
		FROM	SearchQueries
		WHERE	ID = @i;

		EXEC usp_split_string @stmt;
		SET	@i = @i + 1;
	END
END

Execute the above script to accomplish the task.

Check the table for the list of keywords.

Finally capture the keywords and corresponding counts.

SELECT	Word,
	Count(1)AS'Total_Count'
FROM	KeyWords
GROUP BY Word
ORDER BY Total_Count DESC;