Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

query to count repating alphabet in a string Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
declare @asd varchar(20)
set @asd ='asdaaaadffa'
Post #1362523
Posted Friday, September 21, 2012 6:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 12,744, Visits: 31,069
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1362588
Posted Friday, September 21, 2012 6:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
Hello,
I need to count repeating alphabet only
a=6 thats it.
Post #1362595
Posted Friday, September 21, 2012 6:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550

declare @asd varchar(20)
set @asd ='asdaaaadffa'
select len(@asd) - len(replace(@asd,'a',''))



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1362598
Posted Friday, September 21, 2012 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 12,744, Visits: 31,069
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1362599
Posted Friday, September 21, 2012 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 12,744, Visits: 31,069
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1362602
Posted Friday, September 21, 2012 6:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
Cool Guys u rock.
Could you give some good links to learn sql basics
Post #1362604
Posted Friday, September 21, 2012 6:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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!


____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1362606
Posted Friday, September 21, 2012 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 12,744, Visits: 31,069
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

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


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1362608
Posted Friday, September 21, 2012 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1362657
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse