How to generate all Possible Alphabit pattern in a sequence?

Question

Post reply

How to generate all Possible Alphabit pattern in a sequence?

Viewing 7 posts - 16 through 21 (of 21 total)

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

iBar SSCertifiable Points: 5872 More actions · Answer 1

Thanks Jeff, Mark and SQLRNNR for providing such a great ideas. Real Champs!!!

Cheers

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

Heh all finished? Here's another way:

SELECT String = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', start, length)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) t1 (start)

CROSS JOIN (VALUES (26),(25),(24),(23),(22),(21),(20),(19),(18),(17),(16),(15),(14),(13),(12),(11),(10),(9),(8),(7),(6),(5),(4),(3),(2),(1)) t2 (length)

WHERE start + length < 28

ORDER BY start, length

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

It's easy to think Sort = bad, but that's not necessarily the case, just...often.

SET STATISTICS TIME OFF;

PRINT '===== Mark''s Code (98% of batch) ================================================================================='

SET STATISTICS TIME ON;

WITH AllAlpha(Letter) AS (

SELECT CHAR(ASCII('A')+N)

FROM dbo.Tally

WHERE N BETWEEN 0 AND 25)

SELECT (SELECT c.Letter AS "text()"

FROM AllAlpha c

WHERE c.Letter BETWEEN a.Letter AND b.Letter

ORDER BY c.Letter

FOR XML PATH('')) AS Perms

FROM AllAlpha a

INNER JOIN AllAlpha b ON b.Letter>=a.Letter

ORDER BY Perms;

SET STATISTICS TIME OFF;

PRINT '===== Another way (1% of batch) =================================================================================='

SET STATISTICS TIME ON;

SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',t1.N,t2.N-t1.N+1)

FROM dbo.Tally t1,

dbo.Tally t2

WHERE t1.N BETWEEN 1 AND 26

AND t2.N BETWEEN 1 AND 26

AND t1.N <= t2.N

ORDER BY t1.N, t2.N;

SET STATISTICS TIME OFF;

PRINT '===== Rowconstructor 1 (no Sort in Actual Plan) (1% of batch) =================================================================================='

SET STATISTICS TIME ON;

SELECT String = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', start, length)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) t1 (start)

CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) t2 (length)

WHERE start + length < 28

SET STATISTICS TIME OFF;

PRINT '===== Rowconstructor 2 (Sort in Actual Plan) (0% of batch) =================================================================================='

SET STATISTICS TIME ON;

SELECT String = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', start, length)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) t1 (start)

CROSS JOIN (VALUES (26),(25),(24),(23),(22),(21),(20),(19),(18),(17),(16),(15),(14),(13),(12),(11),(10),(9),(8),(7),(6),(5),(4),(3),(2),(1)) t2 (length)

WHERE start + length < 28

ORDER BY start, length

SET STATISTICS TIME OFF;

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Wow - nice work Chris

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

iBar SSCertifiable Points: 5872 More actions · Answer 5

Hi Chris,

Sorry for early conclusion but you done a great job as well.

Chris, Mark and Jeff .... you people have shared some real exceptional work.

I really enjoyed this.

thanks for sharing your thoughts and great work.

cheers.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SQLRNNR (10/20/2011)
Wow - nice work Chris

Gosh, thanks Jason. I have to admit though, I don't know why the Sort is displayed as such a high cost in the plan when it's not borne out in the timings. But then - it's not a straightforward join between two tables 😉

^{“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

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

CELKO (10/22/2011)
Let's assume that you have the usual Series table with the integers 1 to (n).
SELECT SUBSTRING ('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
Start.seq, Final.seq)
FROM Alphabet,
Series AS Start, Series AS Final
WHERE Start.seq <= 26
AND Final.seq <= 26
AND Start.seq <= Final.seq;
Do no t use a recursive CTE; it is just a while loop in a thin disguise.

Considering that you have an alphabet string, why is there a need for the Alphabet table?

Well said on the problem with recursive CTE's... they're actually worse than While loops when it comes to resource usage and IO.

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