Split a string and then concat words in SQL

Question

0.00/5 (No votes)

See more:

Hello, I have 2 tables:

Account(ID(PK), Name, Country)
AccountSubstrings(accSubstringID(PK), AccountID, substring)

In the table Account, the column 'Name', I need to split the string to separate words and then concat the separated words starting with a different first word each time. These new substrings will be inserted s a new row in the table AccountSubstrings.

I wish to make a function or stored procedure to do this as I will be passing in @Name from the Account table.

For example one 'Name' in the Account table:
@Name = 'This is a sentence'

This needs to be separated as such:

This
Thisis
Thisisa
Thisisasentence
is
isa
isasentence
a
asentence
sentence

If anyone knows how I could acheive this as a function to pass in my column please let me know!Thanks!

What I have tried:

I was given help to get the following but I cannot set it up correctly as a function or to pass a column rather than just accepting a string. It also does not work if there are duplicate words in the sentence, but it works well:

SQL

declare @s varchar(max) = 'This is a sentence';

with words as (
      select s.value as word, row_number() over (order by charindex(s.value, @s)) as seqnum
      from string_split(@s, ' ') s
     ),
     cte as (
      select seqnum, word as combined, format(seqnum, '000') as seqnums
      from words
      union all
      select w.seqnum, concat(cte.combined, ' ', w.word), concat(seqnums, ':', format(w.seqnum, '000'))
      from cte join
           words w
           on w.seqnum = cte.seqnum + 1
     )
select *
from cte
order by seqnums;

Posted 4-Apr-20 13:42pm

CPuser2020

Updated 5-Apr-20 4:42am

Maciej Los

v2

Add a Solution

Comments

Richard MacCutchan 5-Apr-20 4:47am

Split the string into an array.

For each word in array
    For each nextword in array + 1
        Append nextword to word

2 solutions

Solution 1

Check this out:

SQL

DECLARE @s VARCHAR(150) = 'This is a sentence';

DECLARE @tmp TABLE (WordID INT IDENTITY(1,1), Sentence VARCHAR(150), Word VARCHAR(30), DelimPos INT)

INSERT INTO @tmp (Sentence, Word, DelimPos)
SELECT @s, s.value, CHARINDEX(s.value, @s)
FROM STRING_SPLIT(@s, ' ') s;


WITH CTE AS
(
	--initial query
	SELECT 1 AS LoopNo, WordID, Sentence, Word, DelimPos, CONVERT(VARCHAR(MAX), Word) As NewSentence
	FROM @tmp
	--recursive part
	UNION ALL
	SELECT LoopNo + 1 AS LoopNo, t2.WordID, t2.Sentence, t2.Word, t2.DelimPos, CONCAT(NewSentence, t2.Word) AS NewSentence
	FROM CTE t1 INNER JOIN @tmp t2 ON t1.WordID  +1 =  t2.WordID 
	WHERE t1.WordID<= LoopNo +2
)
SELECT LoopNo, RowNo, NewSentence
FROM
(
	SELECT LoopNo, WordID, Word, DelimPos, NewSentence, DENSE_RANK() OVER(PARTITION BY LoopNo ORDER BY WordID) RowNo
	FROM CTE 
) T
ORDER BY RowNo, DelimPos

Result:

LoopNo	RowNo	NewSentence
1		1		This
2		1		Thisis
3		1		Thisisa
4		1		Thisisasentence
1		2		is
2		2		isa
3		2		isasentence
1		3		a
2		3		asentence
1		4		sentence

;)

Posted 5-Apr-20 0:09am

Maciej Los

Updated 5-Apr-20 0:25am

v2

Comments

CPuser2020 5-Apr-20 7:18am

Hi Maciej, thanks very much for your help. I am trying to create it as a function so that I can pass in the column 'Name' and it would do the above to each row. I am trying to change your answer around to do this but it is giving me erros that I cannot return a select statement from a function

Maciej Los 5-Apr-20 7:46am

Follow,the instruction: Create User-defined Functions (Database Engine) - SQL Server | Microsoft Docs[^]
I believe my answer resolves your issue, even if you can't used it inside a function...
So, can you accept it to remove your question from unanswered list?

CPuser2020 5-Apr-20 8:06am

Hi Maciej, thanks very much. Although it works for 'This is a sentence' it still doesnt answer my question the way it needs to. I need it to take in various strings from the column 'Name' in the table 'Account'. 'This is a sentence' was given as an example of how I need the strings to look. The solution I added in my question also gave the desired output but doesnt take in the column and thats why I posted it here. Thanks for the article, I had previusly read this

Maciej Los 5-Apr-20 10:42am

See solution#2.

Maciej Los 5-Apr-20 15:04pm

And...

CPuser2020 5-Apr-20 16:46pm

Hi Maciej, I'm only getting a chance to try this now. It works, thanks very much for your help, much appreciated

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-04-05T04:43:00

Accordingly to the comments under solution#1...

You have to create a function: Create User-defined Functions (Database Engine) - SQL Server | Microsoft Docs[^]

SQL

USE [test] --my test database, you need to use your database name
GO

IF OBJECT_ID (N'dbo.udf_SentenceToMulti', N'TF') IS NOT NULL  
    DROP FUNCTION dbo.udf_SentenceToMulti;  
GO  


--@AccountId refers to ID field in Account table
--@Name refers to Name field in Account table
CREATE FUNCTION [dbo].[udf_SentenceToMulti](@AccountId INT, @Name VARCHAR(150))
RETURNS @tmpTable TABLE 
(
	SubSentenceId INT PRIMARY KEY NOT NULL, 
	AccountId INT NOT NULL,
	NewSentence VARCHAR(MAX) NOT NULL
)
AS
BEGIN
	WITH InitialTable AS
	(
		SELECT @AccountId AS AccountId,  s.value AS Word, ROW_NUMBER() OVER(ORDER BY CHARINDEX(s.value, @Name)) AS WordId
		FROM STRING_SPLIT(@Name, ' ') s
	), CTE AS
	(
		--initial query
		SELECT 1 AS LoopNo, AccountId, WordID, Word, CONVERT(VARCHAR(MAX), Word) As NewSentence
		FROM InitialTable
		--recursive part
		UNION ALL
		SELECT LoopNo + 1 AS LoopNo, t2.AccountId, t2.WordID, t2.Word, CONCAT(NewSentence, t2.Word) AS NewSentence
		FROM CTE t1 INNER JOIN InitialTable t2 ON t1.WordID  +1 =  t2.WordID 
		WHERE t1.WordID<= LoopNo +2
	)
	INSERT INTO @tmpTable 
	SELECT ROW_NUMBER() OVER(ORDER BY RowNo, LoopNo) AS SubSentenceId, AccountID, NewSentence
	FROM
	(
		SELECT WordID, AccountId, NewSentence, LoopNo, DENSE_RANK() OVER(PARTITION BY LoopNo ORDER BY WordID) RowNo
		FROM CTE 
	) T
	ORDER BY RowNo, LoopNo
	RETURN
END;
GO

Usage:

SQL

USE test; --replace with your database name

;WITH AllSentences AS
(
	SELECT ID, [Name]
	FROM Account 
)
SELECT a.ID, a.[Name], b.NewSentence
FROM AllSentences a
CROSS APPLY udf_SentenceToMulti(a.ID, a.[Name]) b

Example result:

1	This is a sentence	This
1	This is a sentence	Thisis
1	This is a sentence	Thisisa
1	This is a sentence	Thisisasentence
1	This is a sentence	is
1	This is a sentence	isa
1	This is a sentence	isasentence
1	This is a sentence	a
1	This is a sentence	asentence
1	This is a sentence	sentence
2	This is another sentence	This
2	This is another sentence	Thisis
2	This is another sentence	Thisisanother
2	This is another sentence	Thisisanothersentence
2	This is another sentence	is
2	This is another sentence	isanother
2	This is another sentence	isanothersentence
2	This is another sentence	another
2	This is another sentence	anothersentence
2	This is another sentence	sentence
...