How to find out the count of individual chars

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to find out the count of individual chars

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-14 : 03:05:28

Hi,
Can any one tell me
how to find out the count of individual chars in a String

ie., my string is s='ganesh kumar'
my output must be as below

Character Occurance
--------------------
g 1
a 2
n 1
e 1
s 1
h 1
k 1
u 1
m 1
r 1
----------------------

Can any one do it,
This is interview question..

Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 03:11:20

[code]DECLARE @s VARCHAR(250)

SET @s = 'ganesh kumar'

SELECT SUBSTRING(@s, Number, 1),
COUNT(*)
FROM master..spt_values
WHERE Type = 'p'
AND Number BETWEEN 1 AND LEN(@s)
GROUP BY SUBSTRING(@s, Number, 1)
ORDER BY SUBSTRING(@s, Number, 1)[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 03:28:19

small modifications:-

DECLARE	@s VARCHAR(250)

SET	@s = 'ganesh kumar M'

SELECT		SUBSTRING(@s, Number, 1),
		COUNT(*)
FROM		master..spt_values
WHERE		Type = 'p'
		AND Number BETWEEN 1 AND LEN(@s)
		AND SUBSTRING(@s, Number, 1) LIKE '%[a-zA-Z]%'GROUP BY	SUBSTRING(@s, Number, 1)
ORDER BY	CHARINDEX(SUBSTRING(@s, Number, 1),@s)

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-14 : 03:32:55

Thank you very much,

But i cant get you..
Can you explain..

Ganesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 03:36:09

I get error message "Column name 'master..spt_values.number' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause." when trying your improvement, Visakh.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 03:38:10

A simple ORDER BY MIN(Number) will do.

DECLARE	@s VARCHAR(250)

SET	@s = 'ganesh kumar'

SELECT		SUBSTRING(@s, Number, 1),
		COUNT(*)
FROM		master..spt_values
WHERE		Type = 'p'
		AND Number BETWEEN 1 AND LEN(@s)
		AND SUBSTRING(@s, Number, 1) LIKE '[a-zA-Z]'
GROUP BY	SUBSTRING(@s, Number, 1)
ORDER BY	MIN(Number)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 03:50:12

quote:
Originally posted by Peso

I get error message "Column name 'master..spt_values.number' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause." when trying your improvement, Visakh.

E 12°55'05.25"
N 56°04'39.16"

But it worked for me...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 04:17:46

Works on SQL Server 2005. Does not on SQL Server 2000.
My work on both platforms.

And since this is a SQL Server 2005 forum, you are right.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources