Qurey syntax problem

Question

I have a string which looks like

%AP|TN|UP%%10|20%

I am trying to write some query 

My problem is I want a  strings in following format 

AP10

AP20

TN10

TN20

UP10

UP20

My string is dynamic sometimes it may look like as follows 

%AP|TN|UP%%10|20%%aa|bb%

then it should give me all the possible combinations of it . I was trying to use this to write generate query to my database. 

Any help will be really helpful  THanks in Advance

Answer 1

Could you indicate what type of database you are working with?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Hi Query Syntax problem,

Welcome to MSDN Forum.

Have you solved the issue? I look forward to hearing from you. If not, could you please post what's the type of database you are using? This is, so we can help you more effectively.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 3

What language do you want to write this query in? Such complex pattern can not be written in T-SQL directly, you need to use RegEX.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Mostly guessing based on your explanation, it looks as if you have a set of delimiter separated values separated by the pipe character, that falls within an outer set of delimiter separated groups, delimited by '%', with a little extra caveat that there's a double '%%' when one group ends and another starts.

And from there, a cartesian join to show all possible permutations of each group.  Right?  You can do this with a little extra effort, using the variations of the popular SPLIT functions that are talked about here quite often.

Is there a limitless number of outer groups?  Or just three in your "%AP|TN|UP%%10|20%%aa|bb%" example?

There was a thread in here just yesterday, someone else with a nested set of delimiter separated values within a string.   In your case, I'd suggest starting by making your life a little easier, and replace all '%%' with a single '%'.  I'll look up that thread, and post it back here via an edit on this reply.

EDIT: This was the thread from yesterday, where there was a split group inside another split group.  http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/fd7adce4-4812-4bbc-9307-9aabf822aded

Answer 5

Well, that was fun:

Declare @test Table (
	String varchar(100)
	)

Insert @test
--Values ('%AP|TN|UP%%10|20%')
Values ('%AP|TN|UP%%10|20%%aa|bb%')

;with cte1(Col1,Rnk) As (
	Select
		s.Item
		,ROW_NUMBER() OVER (ORDER BY s.ItemNumber)
	From
		@test t
	cross apply dbo.fncDelimitedSplit8K(String, '%') s
	Where
		s.Item <> ''
	),
cte2(Col1,Grp,Rnk) As (
	Select
		s.Item
		,c.Rnk
		,ROW_NUMBER() OVER (PARTITION BY c.Rnk ORDER BY c.Rnk)
	From cte1 c
	cross apply dbo.fncDelimitedSplit8K(c.Col1, '|') s
	Where
		s.Item <> ''
	)
	
Select
	c1.Col1 + c2.Col1 + c3.Col1 AS foo
From		cte2 c1
full join	cte2 c2
		on	1=1
full join	cte2 c3
		on	1=1
Where
	c2.Col1 <> c1.Col1
And c3.Col1 <> c2.Col1
And c3.Col1 <> c1.Col1
And c1.Grp = 1
And c2.Grp = 2
And c3.Grp = 3
Order By foo
/*
foo
-------
AP10aa
AP10bb
AP20aa
AP20bb
TN10aa
TN10bb
TN20aa
TN20bb
UP10aa
UP10bb
UP20aa
UP20bb
*/

Edit:  Forgot to mention this will only work for the second example.  But it works, so you should be able to adjust to fit your needs.  Or just use dynamic SQL.  I'm not very good with that, but I imagine it's possible?

Answer 6

I'm also partly (a) stumped and (b) it's 5:30!, so here is a dual split into groups, with the remaining to-do item being the cartesian join.  I think a recursive CTE as the final step will do the job,  I'll do that later (HOWEVER... if the business rule knows just one, two, or three groups, maybe avoiding that complexity is a good idea?)

With InitString (InStr)   as  /* Get red of dual '%%' up front */
    (Select Replace('%AP|TN|UP%%10|20%%aa|bb%', '%%', '%'))
, SeqList (SeqNo)  as  /* One of many ways to create a sequence list */
    (Select top 100 Row_Number() over(Order by @@ServerName) from SYS.MESSAGES)
, GroupList  as  /* Split into groups delimited by '%', and preface each group with '|' for next step (makes easier) */
  (Select '|' + SubString(InStr, SeqNo + 1, NextPercent - SeqNo - 1) as CurrGroup
     from SeqList S 
    inner Join InitString on SubString(InStr, SeqNo, 1) = '%' 
    Cross Apply (Select CharIndex('%', Instr, SeqNo + 1) as NextPercent) CA1
    Where SeqNo < Len(Instr)
   )
 , WordList as /* For each group, split into words delimited by '|' */
   (
	Select *, SubString(CurrGroup, SeqNo + 1, EndPos - SeqNo - 1) as ThisItem
	  From SEQLIST SL 
	 inner join GroupList GL on SubString(CurrGroup, SeqNo, 1) = '|'
	 Cross Apply (Select CharIndex('|', CurrGroup, SeqNo + 1) as NextBar, Len(CurrGroup) as LenGroup) CA1
	 Cross Apply (Select Case NextBar when 0 then LenGroup + 1 else NextBar End as EndPos) CA2
	 Where SeqNo < Len(CurrGroup)
   )  
Select Dense_Rank() over(order by currgroup) as GroupNo, CurrGroup, ThisItem  /* This is just a list of all the values, with a group number) */
  from WordList
  
  

Answer 7

Here we go.  This is ugly as h***, but it works, assuming the two options are two elements or three.  Don't let CELKO see it, I'm afraid of him.

Declare @test Table (
	String varchar(30)
	)

Declare @cte Table (
	Col1 varchar(30)
	,Grp int
	,Rnk int
	)
Declare @String varchar(30)
Declare @Elements int

Insert @test
Values ('%AP|TN|UP%%10|20%')
Insert @test
Values ('%AP|TN|UP%%10|20%%aa|bb%')

Declare testCursor Cursor STATIC FORWARD_ONLY For
Select String From @test

Open testCursor
Fetch Next From testCursor Into @String

WHILE @@FETCH_STATUS = 0
Begin
	Select @Elements = MAX(tbl.Element)
	From	(
		Select
			s.Item
			,ROW_NUMBER() OVER (ORDER BY s.ItemNumber) As Element
		From
			dbo.fncDelimitedSplit8K(@String, '%') s
		Where
			s.Item <> ''
		) tbl

	;with cte1(Col1,Rnk) As (
		Select
			s.Item
			,ROW_NUMBER() OVER (ORDER BY s.ItemNumber)
		From dbo.fncDelimitedSplit8K(@String, '%') s
		Where
			s.Item <> ''
		),
	cte2(Col1,Grp,Rnk) As (
		Select
			s.Item
			,c.Rnk
			,ROW_NUMBER() OVER (PARTITION BY c.Rnk ORDER BY c.Rnk)
		From cte1 c
		cross apply dbo.fncDelimitedSplit8K(c.Col1, '|') s
		Where
			s.Item <> ''
		)
	
	Insert @cte
	Select Col1,Grp,Rnk From cte2
	Order By Grp
	
	If @Elements = 3
	Begin
		Select
			c1.Col1 + c2.Col1 + c3.Col1 AS foo
		From		@cte c1
		full join	@cte c2
				on	1=1
		full join	@cte c3
				on	1=1
		Where
			c2.Col1 <> c1.Col1
		And c3.Col1 <> c2.Col1
		And c3.Col1 <> c1.Col1
		And c1.Grp = 1
		And c2.Grp = 2
		And c3.Grp = 3
		Order By foo
	End
	Else
	Begin
		Select
			c1.Col1 + c2.Col1 As foo
		From		@cte c1
		full join	@cte c2
				on	1=1
		Where
			c2.Col1 <> c1.Col1
		And c1.Grp = 1
		And c2.Grp = 2
		Order By foo
	End
	
	Delete From @cte
	
Fetch Next From testCursor Into @String
End

Close testCursor
Deallocate testCursor

@john: I was trying to do it all with just cte's, but they're still a new concept to me.  I got about as far as you did (a table with everything split out, ready to be concatenated back together), and couldn't figure out how to finish the job with another cte.  Then I read your bit about the cartesian join, and ended up with that first post I made.

Answer 8

Darn cobol seventies assembler sixties machine language fifties adding-machine forties slide-rule thirties pencil-and-paper twenties jaquards-loom 1800s coding style!   (Kidding, of course!  :-)  It's almost Friday!

Answer 9

Here's a way, using a recursive CTE.  For such a short question, the answer's gotten pretty complex.  It also makes me wonder, were the delimiter separated values within delimiter separated groups in a relational table to begin with?  Was this even the original posters question?  Nonetheless, an interesting puzzle.  :-)

With InitString (InStr)   as  /* Get red of dual '%%' up front */
    (Select Replace('%AP|TN|UP%%10|20%%aa|bb%', '%%', '%')
	)
, SeqList (SeqNo)  as  /* One of many ways to create a sequence list */
    (Select top 100 Row_Number() over(Order by @@ServerName) from SYS.MESSAGES)
, GroupList  as  /* Split into groups delimited by '%', and preface each group with '|' for next step (makes easier) */
  (Select Row_Number() over(Order by @@ServerName) as GroupNo
          , '|' + SubString(InStr, SeqNo + 1, NextPercent - SeqNo - 1) as CurrGroup
     from SeqList S 
    inner Join InitString on SubString(InStr, SeqNo, 1) = '%' 
    Cross Apply (Select CharIndex('%', Instr, SeqNo + 1) as NextPercent) CA1
    Where SeqNo < Len(Instr)
   ) --Select * from GroupList
 , WordList as /* For each group, split into words delimited by '|' */
   (
	Select *, SubString(CurrGroup, SeqNo + 1, EndPos - SeqNo - 1) as ThisItem
	  From SEQLIST SL 
	 inner join GroupList GL on SubString(CurrGroup, SeqNo, 1) = '|'
	 Cross Apply (Select CharIndex('|', CurrGroup, SeqNo + 1) as NextBar, Len(CurrGroup) as LenGroup) CA1
	 Cross Apply (Select Case NextBar when 0 then LenGroup + 1 else NextBar End as EndPos) CA2
	 Where SeqNo < Len(CurrGroup)
   )  
  , JoinList as   /* Recursive CTE, pulls in all permutations with each pass */
   (
    Select 1 as PassNo, GroupNo, CurrGroup, ThisItem
	  From WordList WL   Where GroupNo = 1  
	UNION all
	Select JL.PassNo + 1, WL.GroupNo, WL.CurrGroup, JL.ThisItem + WL.ThisItem
	  From WordList WL
	inner join JoinList JL on wL.GroupNo = jL.PassNo  + 1
   ) 
   Select Thisitem 
     from JoinLIst
    where PassNo = (Select max(GroupNo) from WordList)  /* Pull in only the final pass */
	Order by ThisItem
 

Answer 10

Ahhh! Don't do that.

Yes, I had a lot of fun putting that together.  Would be nice to hear from the OP to find out how we did.  Might try to find some time today to make mine work using something more akin to your approach, since yours works regardless of the number of elements.

Edit: I stole john's cte and modified my original query using its logic.  Also, just to make sure it worked with all possibilities, I added an extra element.  Fun times.

Declare @String varchar(50)
Set @String = '%AP|TN|UP%%10|20%%aa|bb%%xxx|yyy|zzz%'
--Set @String = '%AP|TN|UP%%10|20%%aa|bb%'
--Set @String = '%AP|TN|UP%%10|20%'

;with cteGrp As (
	Select	ROW_NUMBER() OVER (ORDER BY s.ItemNumber) Grp
			,s.Item
	From	dbo.fncDelimitedSplit8K(@String, '%') s
	Where	s.Item <> ''
	) --select * from cteGrp
,cteElements As (
	Select
		cg.Grp Grp
		,cg.Item GrpValue
		,ROW_NUMBER() OVER (ORDER BY cg.Grp) Element
		,s.Item
	From
		cteGrp cg
	cross apply dbo.fncDelimitedSplit8K(cg.Item, '|') s
	) --select * from cteElements
,ctePossibles As (
	Select	Grp
			,GrpValue
			,Element
			,Item
	From	cteElements ce
	WHERE	ce.Grp = 1
	UNION ALL
	Select	ce.Grp
			,ce.GrpValue
			,ce.Element + 1
			,cp.Item + ce.item
	From	cteElements ce
	join	ctePossibles cp
		on	ce.Grp = cp.Grp + 1
	) --select * from ctePossibles
	
Select	Item
From	ctePossibles
Where	Grp = (Select MAX(Grp) From ctePossibles)
Order By Item