find occurrence of character - 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

find occurrence of character

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-01-09 : 03:34:28

Hi All,

I want to find the occurrence for the character in a string
Like:
Declare @A varchar(50)
Set @A='aaaabbbbcccccdd'

In this case how to get output as:
A 4
B 4
C 5
D 2
Case is not sensitive and value is not static.

Please help!

Pushkar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 03:46:17

you will need a tally / number table. If you do not have one,you can use the spt_values or F_TABLE_NUMBER_RANGE


Declare @A varchar(50)
Set @A='aaaabbbbcccccdd'

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

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

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-01-09 : 05:00:29

quote:
Originally posted by khtan

you will need a tally / number table. If you do not have one,you can use the spt_values or F_TABLE_NUMBER_RANGE

Declare @A varchar(50)
Set @A='aaaabbbbcccccdd'

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

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

well this one is accurate answer but is there any other alternative means some logic that does not use master... but still i should thanks you here

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 05:15:26

quote:
is there any other alternative means some logic that does not use master

As suggested, use a tally / number table or the F_TABLE_NUBMER_RANGE !

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

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-01-10 : 04:39:24

One alternative is using Recursive CTEs as below


;WITH CTE AS 
(
SELECT substring(@A, 1, 1) AS LETTER,2 AS NUMBER
UNION ALL
SELECT substring(@A, C.NUMBER, 1),C.NUMBER+1 NUMBER FROM CTE C
WHERE C.NUMBER<=LEN(@A)
)
SELECT LETTER,COUNT(*) AS CNT 
FROM CTE
GROUP BY LETTER
OPTION (MAXRECURSION 0)

--Ranjit

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-01-10 : 05:45:05

quote:

Originally posted by Ranjit.ileni

One alternative is using Recursive CTEs as below


;WITH CTE AS 
(
SELECT substring(@A, 1, 1) AS LETTER,2 AS NUMBER
UNION ALL
SELECT substring(@A, C.NUMBER, 1),C.NUMBER+1 NUMBER FROM CTE C
WHERE C.NUMBER<=LEN(@A)
)
SELECT LETTER,COUNT(*) AS CNT 
FROM CTE
GROUP BY LETTER
OPTION (MAXRECURSION 0)

--Ranjit

That is what I was looking for ......... Thank you Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-01-10 : 06:19:44

You are welcome

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources