Click here to Skip to main content
14,743,426 members
Please Sign up or sign in to vote.
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:

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
Updated 5-Apr-20 5:42am
v2
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

Check this out:
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


;)
   
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
Accordingly to the comments under solution#1...

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

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:
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
...
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900