MS SQL String combination

Question

Post reply

MS SQL String combination

Viewing 10 posts - 16 through 24 (of 24 total)

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

samuelg78 Old Hand Points: 338 More actions · Answer 1

samuelg78

Old Hand

Points: 338

March 8, 2011 at 11:14 pm

#1296318

Thks.

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

Agh. Dammit. 😛 I just can't sleep when such an interesting and fun problem is at hand.

Ok, here's what you do. Execute the following code to build a function that solves all this. Since I was actually able to pull this off in a single query without dynamic SQL, it's a high speed iTVF which may be used in a CROSS APPLY against a whole column of such strings as you have provided. 😀

CREATE FUNCTION dbo.FindCombos

(@pString VARCHAR(8000))

RETURNS TABLE AS

RETURN

WITH

cteSplit AS

(

SELECT BitValue = POWER(2,t.N/3), Element = SUBSTRING(@pString,t.N+1,2)

FROM dbo.Tally t

WHERE t.N BETWEEN 0 AND LEN(@pString)-1

AND t.N%3 = 0

),

cteEnumerateElements AS

(

SELECT t2.N,Element

FROM dbo.Tally t2

INNER JOIN cteSplit split

ON t2.N&split.BitValue > 0

WHERE t2.N BETWEEN 1 AND POWER(2,LEN(@pString)/3+1)

),

ctePivot AS

(

SELECT Combination = STUFF(

(

SELECT '-'+Element

FROM cteEnumerateElements ee2

WHERE ee2.N = ee1.N

FOR XML PATH('')

)

,1,1,'')

FROM cteEnumerateElements ee1

GROUP BY ee1.N

) --=== Display

SELECT Combination

FROM ctePivot

WHERE LEN(Combination) > 2

;

And then it works (simple example does not use CROSS APPLY)

SELECT Combination

FROM dbo.FindCombos('01-02-04-03')

;

Since NOT sorting the elements in the original string seemed important, I didn't. Here's the output I got for the example you gave.

01-02
01-04
02-04
01-02-04
01-03
02-03
01-02-03
04-03
01-04-03
02-04-03
01-02-04-03

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

p.s. Sort the output by length, by value, or a combination of both to get it the way you want it.

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

U are really helpful and i greatly appreciated it. But encounter the following error:

Invalid object name 'dbo.Tally'

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

My bad. I shouldn't post this late at night. 😛

You need to build a "Zero Based" Tally Table. Here's how to do that...

--===================================================================

-- Create a Tally table from 0 to 11000

--===================================================================

--===== Create and populate the Tally table on the fly.

SELECT TOP 11001

IDENTITY(INT,0,1) AS N

INTO dbo.Tally

FROM Master.sys.ALL_Columns ac1

CROSS JOIN Master.sys.ALL_Columns ac2

;

--===== Add a CLUSTERED Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

;

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

;

GO

A Tally Table replaces the need for certain loops and blows the doors off of loops when it come to performance and ease of coding. My recommendation is that you read the following article to find out more about how Tally Tables work.

http://www.sqlservercentral.com/articles/T-SQL/62867/

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

It worked great. I had tried to post in many forum but you are the only one who can managed to solve it. Great thks.

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 7

Who IS that masked man?

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

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

samuelg78 (3/9/2011)
It worked great. I had tried to post in many forum but you are the only one who can managed to solve it. Great thks.

Thanks, samuelg78. It's nice to hear feedback like that. Thanks for the fun problem. 🙂

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

GPO (3/9/2011)
Who IS that masked man?

Just lil' ol' pork chop slinger, me. 🙂

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

samuelg78

Old Hand

Points: 338

March 9, 2011 at 10:21 am

#1296629

Haha, thks again.