Need table for number of alphabets in expression

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 2008 Forums

Transact-SQL (2008)

Need table for number of alphabets in expression

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-08-11 : 11:45:05

If i have string like 'Transit' then how do I return a table as

T 3
R 2
A 1
N 2
S 1
I 1

-----------------------------------------------------------------------------------------------
Ashley Rhodes

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-11 : 11:51:37

What do those numbers mean?

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-08-11 : 12:32:30

the total number of characters in expression
sorry i got it wrong

TRANSITS

T 2
R 1
A 1
N 1
S 2
I 1

-----------------------------------------------------------------------------------------------
Ashley Rhodes

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-11 : 13:35:24

Here's one way:



declare @s varchar(200)
set @s = 'Transits' 

select c             as letter
       ,count(*)     as letter_count
from   (
       select substring(s, number, 1) c
              ,n.number
       from   (select @s [s]) d
       join   master..spt_values n
              on n.type = 'P'
              and n.number between 1 and len(s)
       ) d
group by c
order by min(number)

OUTPUT:

letter letter_count
------ ------------
t      2
r      1
a      1
n      1
s      2
i      1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources