MS SQL String combination

Question

Post reply

MS SQL String combination

samuelg78

Old Hand

Points: 338
More actions
March 8, 2011 at 9:06 pm

#389007

I will like to return a table of rows consist of all possible combination of a string. For example string "01-02-03-04":
1. 01 - 02 - 03 - 04
2. 02 - 03 - 04
3. 01 - 03 - 04
4. 01 - 02 - 04
5. 01 - 02 - 03
6. 01 - 02
7. 01 - 03
8. 01 - 04
9. 02 - 03
10. 02 - 04
11. 03 - 04
Any help will be much appreciated. Thks in advance.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 1

Is this the sort of thing you're getting at?

IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL

DROP TABLE #test;

select cast('01' as char(2)) as col1 into #test union all

select cast('02' as char(2)) as col1 union all

select cast('03' as char(2)) as col1 union all

select cast('04' as char(2)) as col1;

select t1.col1 + ' - ' + t2.col1 + ' - ' + t3.col1 + ' - ' + t4.col1 as result

from #test t1

cross join #test t2

cross join #test t3

cross join #test t4

UNION ALL

select t1.col1 + ' - ' + t2.col1 + ' - ' + t3.col1

from #test t1

cross join #test t2

cross join #test t3

UNION ALL

select t1.col1 + ' - ' + t2.col1

from #test t1

cross join #test t2

UNION ALL

select t1.col1

from #test t1

order by result

Cheers

GPO

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

samuelg78 Old Hand Points: 338 More actions · Answer 2

Thks for the fast response. But your query return 340 rows instead of 11 rows. Pls also take note that i may be able to pass in "01-02-03-04-05".

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 3

OK. So what are the rules around "all possible combination"?

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 4

Now that I think about it:

:: What to the incoming strings represent?

:: What do the numbers in the incoming strings represent?

:: Is the delimiter on the incoming string always a "-" (a dash)?

:: Will the incoming string every have a leading or trailing dash delimiter?

:: Are the numbers in the incoming strings necessarily in order?

:: Are the numbers in the incoming strings necessarily unique?

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

samuelg78 Old Hand Points: 338 More actions · Answer 5

For example string "01-02-03-04" will return 11 rows:

1. 01 - 02 - 03 - 04

2. 02 - 03 - 04

3. 01 - 03 - 04

4. 01 - 02 - 04

5. 01 - 02 - 03

6. 01 - 02

7. 01 - 03

8. 01 - 04

9. 02 - 03

10. 02 - 04

11. 03 - 04

For example string "01-02-03-04-05" will return 26 rows:

1. 01 - 02 - 03 - 04

2. 01 - 02 - 03 - 05

3. 01 - 02 - 04 - 05

4. 01 - 03 - 04 - 05

5. 02 - 03 - 04 - 05

6. 01 - 02 - 03

7. 01 - 02 - 04

8. 01 - 02 - 05

9. 01 - 03 - 04

10. 01 - 03 - 05

11. 01 - 04 - 05

12. 02 - 03 - 04

13. 02 - 03 - 05

14. 02 - 04 - 05

15. 03 - 04 - 05

16. 01 - 02

17. 01 - 03

18. 01 - 04

19. 01 - 05

20. 02 - 03

21. 02 - 04

22. 02 - 05

23. 03 - 04

24. 03 - 05

25. 04 - 05

26. 01-02-03-04-05

Pls take note that string arrangement will be ascending.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

What is the maximum number of elements in any given string?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

Also, will each element have the same number of characters as the other elements in the same string?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

samuelg78 Old Hand Points: 338 More actions · Answer 8

The maximum number of elements will be 10. And each element is unique. And the pass in string maybe not be in order e.g: "01-02-04-03", "03-02-01".

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

samuelg78 (3/8/2011)
The maximum number of elements will be 10. And each element is unique. And the pass in string maybe not be in order e.g: "01-02-04-03", "03-02-01".

Understood. But I still need to know, will the width of each element always be 2?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 10

So (to labour the point) if the incoming string is "01-06-05-04" then:

:: "01" is not a valid answer?

:: "01-04" is considered to be the same as "04-01"?

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

samuelg78 Old Hand Points: 338 More actions · Answer 11

samuelg78

Old Hand

Points: 338

March 8, 2011 at 10:53 pm

#1296311

Yes. The width of each element is always 2.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 12

Agh! Look at the bloody time. It's 1:00AM here. I've gotta get up in 4 hours.

To solve this problem, think "Binary Counter" with a filter. For example, for a 3 element string, consider 3 bits. A counter from 1 to 2^3-1 will ratchet through all of the bits. Then all you need to do is filter out the counts that only use 1 bit and Bob's your uncle. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

samuelg78 Old Hand Points: 338 More actions · Answer 13

I am not familiar with binary. Pls help me out when you are free. Thks

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

...and I apologize for copping out for sleep. If no one solves this for you tonight or tomorrow, I'll try to get to it after my local PASS meeting tomorrow night.

Because of the variable width nature of the overall string, I believe we made a touch of dynamic SQL.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)