Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cte for String in random order Expand / Collapse
Author
Message
Posted Friday, June 27, 2014 9:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:57 AM
Points: 41, Visits: 394
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

Post #1586934
Posted Friday, June 27, 2014 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 5,490, Visits: 10,354
If you can split it, just order the results by NEW_ID() and reconcatenate.

John
Post #1586940
Posted Friday, June 27, 2014 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:57 AM
Points: 41, Visits: 394
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
Post #1586942
Posted Friday, June 27, 2014 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's 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)
Post #1586945
Posted Friday, June 27, 2014 9:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 PM
Points: 7,179, Visits: 15,781
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?
Post #1587087
Posted Saturday, June 28, 2014 12:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:57 AM
Points: 41, Visits: 394
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

)
Post #1587097
Posted Saturday, June 28, 2014 8:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 35,808, Visits: 32,481
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1587303
Posted Wednesday, December 10, 2014 11:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 35,808, Visits: 32,481
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1643168
Posted Thursday, December 11, 2014 4:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:57 AM
Points: 41, Visits: 394
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.


Post #1643235
Posted Thursday, December 11, 2014 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1643262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse