query to count repating alphabet in a string

Question

Post reply

query to count repating alphabet in a string

saltpepo

Old Hand

Points: 396
More actions
September 21, 2012 at 4:12 am

#262712

declare @asd varchar(20)
set @asd ='asdaaaadffa'

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

saltpepo (9/21/2012)
declare @asd varchar(20)
set @asd ='asdaaaadffa'

so do you want a count of the max number of repeats in the string for each letter?

so a=4,s=1,d=1,f=2, or are you after just a single letter?

or the count of all the 'a''s which would be 6?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saltpepo Old Hand Points: 396 More actions · Answer 2

Hello,

I need to count repeating alphabet only

a=6 thats it.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

declare @asd varchar(20)

set @asd ='asdaaaadffa'

select len(@asd) - len(replace(@asd,'a',''))

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lowell SSC Guru Points: 323518 More actions · Answer 4

saltpepo (9/21/2012)
Hello,
I need to count repeating alphabet only
a=6 thats it.

well, 'f' repeats as well in your example.

the easiest way for the count is simply

SELECT LEN(@asd) - LEN(REPLACE(@asd,'a',''))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 5

Mark-101232 (9/21/2012)
declare @asd varchar(20) set @asd ='asdaaaadffa'
select len(@asd) - len(replace(@asd,'a',''))

oh your fast on the draw there, partner!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saltpepo Old Hand Points: 396 More actions · Answer 6

Cool Guys u rock.

Could you give some good links to learn sql basics

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Lowell (9/21/2012)
Mark-101232 (9/21/2012)
declare @asd varchar(20) set @asd ='asdaaaadffa'
select len(@asd) - len(replace(@asd,'a',''))
oh your fast on the draw there, partner!

Yee-Haa!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lowell SSC Guru Points: 323518 More actions · Answer 8

saltpepo (9/21/2012)
Cool Guys u rock.
Could you give some good links to learn sql basics

a lot of great guys here have put together a bunch of "stairways" series on many of the SQL subjects that give great step by steps and walk throughs;

take a look here:

SQL Server Stairways Atricles[/url]

there's one that starts with t-SQL basics, and many on specific usages;

Stairway to TSQL DML[/url]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Big hammer;

DECLARE @asd VARCHAR(20)

SET @asd = 'asdaaaadffa'

SELECT

Letter,

Occurrences = COUNT(*)

FROM (

SELECT Letter = SUBSTRING(@asd,n,1)

FROM (SELECT TOP(LEN(@asd)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns) tally

) d

GROUP BY Letter

ORDER BY Occurrences DESC

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

saltpepo Old Hand Points: 396 More actions · Answer 10

saltpepo

Old Hand

Points: 396

September 21, 2012 at 8:00 am

#1540704

U Nailed It..

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

ChrisM@Work (9/21/2012)
Big hammer;
DECLARE @asd VARCHAR(20)
SET @asd = 'asdaaaadffa'
SELECT
Letter,
Occurrences = COUNT(*)
FROM (
SELECT Letter = SUBSTRING(@asd,n,1)
FROM (SELECT TOP(LEN(@asd)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns) tally
) d
GROUP BY Letter
ORDER BY Occurrences DESC

I'm guessing the OP will get an A+ on this homework assignment.

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