char count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

char count

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-07-31 : 07:57:38

i have a string like aaannbbghtgggbbaa, i want a quey to get count of charecters in string like

a 5
b 4
others 20

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-31 : 08:04:19

[code]
declare @str varchar(100) = 'aaannbbghtgggbbaa'

select substring(@str, number, 1), count(*)
from master..spt_values
where type = 'P'
and number >= 1
and number <= len(@str)
group by substring(@str, number, 1)[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-31 : 08:12:12

quote:

Originally posted by khtan


declare	@str varchar(100) = 'aaannbbghtgggbbaa'

select	substring(@str, number, 1), count(*)
from	master..spt_values
where	type	= 'P'
and	number	>= 1
and	number	<= len(@str)
group by substring(@str, number, 1)

KH
[spoiler]Time is always against us[/spoiler]

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 08:14:42

Generic solution is this


DECLARE @STring varchar(100)
SET @STring='aaannbbghtgggbbaa'


;With CTE
AS
(
SELECT 1 AS N
UNION ALL
SELECT N+1
FROM CTE
WHERE N + 1 < = LEN(@String)
)

SELECT SUBSTRING(@String,N,1),COUNT(*) AS Cnt
FROM CTE 
GROUP BY SUBSTRING(@String,N,1)
ORDER BY SUBSTRING(@String,N,1)

However if you just want to count a's and b's and all others, this is simpler

SELECT CharVal,
LEN(@String) - LEN(REPLACE(@String,CharVal,'')) AS Cnt
FROM (SELECT 'a' AS CharVal
UNION ALL
SELECT 'b'
)t
UNION ALL
SELECT 'Others',
LEN(REPLACE(REPLACE(@String,'a',''),'b','')) AS Cnt

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources