# of characters

Question

# of characters

Viewing 14 posts - 16 through 28 (of 28 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

LOL I honestly didn't really look very closely. The OP said they wanted to see if they could do it without sys.syscolumns. I simply used the version ChrisM wrote and replaced the derived table with tally. 😀

Your points are absolutely right.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CapnHector SSCoach Points: 16143 More actions · Answer 2

If the OP does not have a tally table in there DB they can use the following to create one on the fly with out hitting sys.columns.

;WITH e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 ),

e2(n) AS (SELECT 1 FROM e1 a, e1 b),

e3(n) AS (SELECT 1 FROM e2 a, e2 b),

Tally(n) AS (SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e3)

then run the query with the cte tally table instead of a DB tally table.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

ChrisM@Work (7/26/2011)
pdanes2 (7/26/2011)
ChrisM@Work (7/26/2011)
That's slick, and definitely going in my toolbox. I never would have guessed that could be done with set-based code.
Thanks! Must admit though, it's borrowed from the teachings of Jeff Moden 🙂 Thanks Jeff.

I'm humbled. :blush: Thank you for the kudo, Chris, and nice job!

--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: 1004318 More actions · Answer 4

dwain.c (4/26/2012)
You gotta love the recursive CTE version of this solution, even though it doesn't perform as well as the tally table version.
CREATE TABLE #t (strings VARCHAR(100));
-- Count the unique characters
;WITH Parser (strs, rest, Num) AS (
SELECT SUBSTRING(strings,1,1), SUBSTRING(strings,2,LEN(strings)), 1
FROM #t UNION ALL
SELECT SUBSTRING(rest, 1, 1), SUBSTRING(rest, 2, LEN(rest)), 1
FROM Parser WHERE LEN(rest) > 0
)
SELECT strs, COUNT(Num) AS Count
FROM Parser
GROUP BY strs
DROP TABLE #t
Recursive CTEs! You gotta love their black little hearts even though they're perplexing!

Nope. Don't gotta love 'em. Gotta hate 'em if especially if they're used for counting. See the following if you don't think so...

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

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Jeff Moden (4/29/2012)
dwain.c (4/26/2012)
You gotta love the recursive CTE version of this solution, even though it doesn't perform as well as the tally table version.
CREATE TABLE #t (strings VARCHAR(100));
-- Count the unique characters
;WITH Parser (strs, rest, Num) AS (
SELECT SUBSTRING(strings,1,1), SUBSTRING(strings,2,LEN(strings)), 1
FROM #t UNION ALL
SELECT SUBSTRING(rest, 1, 1), SUBSTRING(rest, 2, LEN(rest)), 1
FROM Parser WHERE LEN(rest) > 0
)
SELECT strs, COUNT(Num) AS Count
FROM Parser
GROUP BY strs
DROP TABLE #t
Recursive CTEs! You gotta love their black little hearts even though they're perplexing!
Nope. Don't gotta love 'em. Gotta hate 'em if especially if they're used for counting. See the following if you don't think so...
http://www.sqlservercentral.com/articles/T-SQL/74118/

Killjoy!

Actually I like writing them because I figure the more I do the better I'll understand them. I did say it wouldn't perform as well (I tested) but it still didn't kill the thrill.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

dwain.c (4/29/2012)
...Actually I like writing them because I figure the more I do the better I'll understand them. I did say it wouldn't perform as well (I tested) but it still didn't kill the thrill.

Craig Freedman's blog is a great place to start.

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

ChrisM@Work (4/30/2012)
dwain.c (4/29/2012)
...Actually I like writing them because I figure the more I do the better I'll understand them. I did say it wouldn't perform as well (I tested) but it still didn't kill the thrill.
Craig Freedman's blog is a great place to start.

Yeah seen that one. Frankly if I see that Manager/Employee example again I think I'm gonna scream. MS BOL only has a couple of others. But the world of recursive CTEs can be much more rich, even if Jeff would argue that counting with them is inefficient (and it is).

My quest is to find that unique CTE that is not covered in BOL examples and performs better than any other alternative that Jeff can think up!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

capn.hector (4/27/2012)
If the OP does not have a tally table in there DB they can use the following to create one on the fly with out hitting sys.columns.
;WITH e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 ),
e2(n) AS (SELECT 1 FROM e1 a, e1 b),
e3(n) AS (SELECT 1 FROM e2 a, e2 b),
Tally(n) AS (SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e3)
then run the query with the cte tally table instead of a DB tally table.

That would do it nicely. You even have the "TOP" clause in there which a lot of folks forget.

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

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

dwain.c (4/30/2012)
ChrisM@Work (4/30/2012)
dwain.c (4/29/2012)
...Actually I like writing them because I figure the more I do the better I'll understand them. I did say it wouldn't perform as well (I tested) but it still didn't kill the thrill.
Craig Freedman's blog is a great place to start.
Yeah seen that one. Frankly if I see that Manager/Employee example again I think I'm gonna scream. MS BOL only has a couple of others. But the world of recursive CTEs can be much more rich, even if Jeff would argue that counting with them is inefficient (and it is).
My quest is to find that unique CTE that is not covered in BOL examples and performs better than any other alternative that Jeff can think up!

Here you go - Paul White's super-fast DISTINCT.

^{“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: 1004318 More actions · Answer 10

ChrisM@Work (4/30/2012)
dwain.c (4/30/2012)
ChrisM@Work (4/30/2012)
dwain.c (4/29/2012)
...Actually I like writing them because I figure the more I do the better I'll understand them. I did say it wouldn't perform as well (I tested) but it still didn't kill the thrill.
Craig Freedman's blog is a great place to start.
Yeah seen that one. Frankly if I see that Manager/Employee example again I think I'm gonna scream. MS BOL only has a couple of others. But the world of recursive CTEs can be much more rich, even if Jeff would argue that counting with them is inefficient (and it is).
My quest is to find that unique CTE that is not covered in BOL examples and performs better than any other alternative that Jeff can think up!
But it doesn't perform better than any alternative. Try it with no dupes and see how dog slow it can be. 😉
Here you go - Paul White's super-fast DISTINCT.

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

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

Jeff Moden (4/30/2012)
ChrisM@Work (4/30/2012)
dwain.c (4/30/2012)
ChrisM@Work (4/30/2012)
dwain.c (4/29/2012)
...Actually I like writing them because I figure the more I do the better I'll understand them. I did say it wouldn't perform as well (I tested) but it still didn't kill the thrill.
Craig Freedman's blog is a great place to start.
Yeah seen that one. Frankly if I see that Manager/Employee example again I think I'm gonna scream. MS BOL only has a couple of others. But the world of recursive CTEs can be much more rich, even if Jeff would argue that counting with them is inefficient (and it is).
My quest is to find that unique CTE that is not covered in BOL examples and performs better than any other alternative that Jeff can think up!
But it doesn't perform better than any alternative. Try it with no dupes and see how dog slow it can be. 😉
Here you go - Paul White's super-fast DISTINCT.

Jeff - why would you DISTINCT a column with no dupes? 😉

^{“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: 1004318 More actions · Answer 12

For the same reason you'd distinct a column with dupes. To find out if there are dupes.

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

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

Jeff Moden (5/1/2012)
For the same reason you'd distinct a column with dupes. To find out if there are dupes.

LOL! You got me 😛

I had an interview last friday morning. The interviewer was a guy from a gig in 2003/2004 who recognised my name on the cv. I remember the company well - it was where and when I started using ssc.

^{“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: 1004318 More actions · Answer 14

ChrisM@Work (5/1/2012)
Jeff Moden (5/1/2012)
For the same reason you'd distinct a column with dupes. To find out if there are dupes.
LOL! You got me 😛
I had an interview last friday morning. The interviewer was a guy from a gig in 2003/2004 who recognised my name on the cv. I remember the company well - it was where and when I started using ssc.

BWAAA-HAAA!!!!! I've had a couple of "Martha Stuart" moments like that, myself. They're always fun in one way or another.

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