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

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: Friday, July 18, 2014 7:28 AM
Points: 40, Visits: 383
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: Today @ 9:29 AM
Points: 5,226, Visits: 9,435
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: Friday, July 18, 2014 7:28 AM
Points: 40, Visits: 383
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: Today @ 9:39 AM
Points: 13,059, Visits: 11,887
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: Today @ 8:42 AM
Points: 7,115, Visits: 14,981
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: Friday, July 18, 2014 7:28 AM
Points: 40, Visits: 383
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: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse