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.
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_valueswhere type = 'P'and number >= 1and 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_valueswhere type = 'P'and number >= 1and 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 thisDECLARE @STring varchar(100)SET @STring='aaannbbghtgggbbaa';With CTEAS(SELECT 1 AS NUNION ALLSELECT N+1FROM CTEWHERE N + 1 < = LEN(@String))SELECT SUBSTRING(@String,N,1),COUNT(*) AS CntFROM 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 simplerSELECT CharVal,LEN(@String) - LEN(REPLACE(@String,CharVal,'')) AS CntFROM (SELECT 'a' AS CharVal UNION ALL SELECT 'b' )tUNION ALLSELECT 'Others',LEN(REPLACE(REPLACE(@String,'a',''),'b','')) AS Cnt[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|