SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cte for String in random order


Cte for String in random order

Author
Message
peter.cox
peter.cox
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 540
hi

anyone got an idea for a CTE that takes a string, and puts it into a table with the words in any order.

so the variable would be 'tesco mixed fruit'

and the data column in the table would be

tesco mixed fruit
mixed fruit tesco
fruit tesco mixed
mixed tesco fruit
fruit mixed tesco
tesco fruit mixed.


easy enough to split - which I'd thought was the requirement! - but it's taken on a new level of complexity...

thanks
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14255 Visits: 15974
If you can split it, just order the results by NEW_ID() and reconcatenate.

John
peter.cox
peter.cox
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 540
but I need it any order, not just the order it came in, or the reverse, but any combination.

and the variable could be up to say six words
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26231 Visits: 17550
peter.cox (6/27/2014)
but I need it any order, not just the order it came in, or the reverse, but any combination.

and the variable could be up to say six words


You can use the DelimitedSplit8K function for this.


declare @SomeString varchar(50) = 'tesco mixed fruit';

with SplitValues as
(
select s.Item
from dbo.DelimitedSplit8K(@SomeString, ' ') s
)

select *
from SplitValues s1
cross join SplitValues s2
where s1.Item <> s2.Item



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12355 Visits: 18574
Building off of Sean's idea - this gives you the same functionality with up to 6 words

Note: since the number of permutations will grow factorially as you expand the number of possible words in a given set, scale at your own risk.


declare @SomeString varchar(50) = 'tesco mixed fruit bob joe frank'

;with
shortTally as (select top 6 Row_number() over (order by (select null)) n from sys.columns),
SplitValues as
(
select Row_number() over (order by (select null)) TokenID, s.Item
from dbo.DelimitedSplit8K(@SomeString, ' ') s
),
TokenCount as (select SUM(n) C from shortTally where n<= (select COUNT(item) from SplitValues) ),
Cartesian as (
select T1.n N1,
case when c>1 then T2.n else 0 end n2,
case when c>3 then T3.n else 0 end n3,
case when c>6 then T4.n else 0 end n4,
case when c>10 then T5.n else 0 end n5,
case when c>15 then T6.n else 0 end n6
from shortTally T1
join shortTally t2 on T1.n<>t2.n
join shortTally t3 on t1.n<>t3.n and t2.n<>t3.n
join shortTally t4 on t1.n<>t4.n and t2.n<>t4.n and t3.n<>t4.n
join shortTally t5 on t1.n<>t5.n and t2.n<>t5.n and t3.n<>t5.n and t4.n<>t5.n
join shortTally t6 on t1.n<>t6.n and t2.n<>t6.n and t3.n<>t6.n and t4.n<>t6.n and t5.n<>t6.n
cross join tokencount
)
select s1.item+
coalesce(' '+s2.item,'')+
coalesce(' '+s3.item,'')+
coalesce(' '+s4.item,'')+
coalesce(' '+s5.item,'')+
coalesce(' '+s6.item,'')
from Cartesian
left join SplitValues s1 on N1=s1.TokenID
left join SplitValues s2 on N2=s2.TokenID
left join SplitValues s3 on N3=s3.TokenID
left join SplitValues s4 on N4=s4.TokenID
left join SplitValues s5 on N5=s5.TokenID
left join SplitValues s6 on N6=s6.TokenID

where N1+n2+n3+n4+n5+n6 = (select C from TokenCount)



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
peter.cox
peter.cox
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 540
funnily enough, last night I came up with a function. I shall compare the two, but many thanks


GO
/****** Object: UserDefinedFunction [dbo].[split_test] Script Date: 28/06/2014 07:57:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[split_test]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
---- SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
---- 'Data' = ltrim(rtrim(SUBSTRING(@String,a.stpos,COALESCE(NULLIF(a.endpos,0),LEN(@String)+1)-a.stpos)))
---- FROM Split a
----CROSS apply split b



, basedata(id, [text])
AS ( SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = ltrim(rtrim(SUBSTRING(@String,a.stpos,COALESCE(NULLIF(a.endpos,0),LEN(@String)+1)-a.stpos)))
FROM Split a
CROSS apply split b),
cte(id, t, x)
AS (SELECT *,
CAST('<foo>' + REPLACE(REPLACE([text],'(','<bar>'),')','</bar>') + '</foo>' AS XML)
FROM basedata)
--SELECT * FROM basedata

SELECT DISTINCT a.text + ' '+ ISNULL(b.text,'') + ' ' + ISNULL(c.text,'') + ' ' + ISNULL(d.text,'') + ' ' + ISNULL(e.text,'') data FROM basedata a
LEFT outer JOIN basedata b ON a.text<>b.text --AND b.text<>c.text
LEFT outer JOIN basedata c ON a.text<>c.text AND c.text<>b.text
LEFT outer JOIN basedata d ON a.text<>d.text AND c.text<>d.text AND d.text<> b.text
LEFT outer JOIN basedata e ON a.text<>e.text AND b.text<>e.text AND d.text<> e.text AND c.text<> e.text

)

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86752 Visits: 41103
peter.cox (6/27/2014)
hi

anyone got an idea for a CTE that takes a string, and puts it into a table with the words in any order.

so the variable would be 'tesco mixed fruit'

and the data column in the table would be

tesco mixed fruit
mixed fruit tesco
fruit tesco mixed
mixed tesco fruit
fruit mixed tesco
tesco fruit mixed.


easy enough to split - which I'd thought was the requirement! - but it's taken on a new level of complexity...

thanks



Now that you have a couple of possible solutions, why do you need to do this? What's the business reason?

p.s. That WHILE loop cleverly disguised as a recursive CTE probably isn't the best idea for a splitter.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86752 Visits: 41103
Two way street here. I know it' been several months but I'm still interested in why you needed to do this.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peter.cox
peter.cox
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 540
Sorry! Been ages.

It was just a business requirement to search a free form field. Obviously with that being the case, people have the ability to put the data in any order, with varying quantities of spaces etc. We need to be able to search everything, just to make sure any possibly variation is pulled up.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16360 Visits: 19554
You may get some ideas from this:
;WITH 
E1 AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E1 a, E1 b)
SELECT *
FROM iTally i
CROSS APPLY (
SELECT Word AS 'data()'
FROM (
SELECT TOP (ABS(n+CHECKSUM(NEWID()))%9+1) word
FROM (
SELECT word = CAST('the' AS VARCHAR(200)) UNION ALL
SELECT 'quick' UNION ALL
SELECT 'brown' UNION ALL
SELECT 'fox' UNION ALL
SELECT 'jumped' UNION ALL
SELECT 'over' UNION ALL
SELECT 'the' UNION ALL
SELECT 'lazy' UNION ALL
SELECT 'dog'
) Words ORDER BY NEWID()) u2
FOR XML PATH('')

) iTVF(Sentence)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search