Find the character's count in a word

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

Script Library

Find the character's count in a word

Author

Topic

karthik0805
Starting Member

14 Posts

Posted - 2012-09-14 : 06:46:26

If you wanna find count of particular character in a string, you can use the following SQL code:

For example: if you wanna find how many 'l' in the word 'sqldeveloper', you can create proc with above code and run the proc as mentioned below.

create proc charcount
@a varchar(8000),@b varchar(8000)
as
declare @i int = 1, @count int =0, @j int=0
select @j=LEN(@b)
while @i<=LEN(@a)
begin
if (select substring(@a,@i,@j))=@b
begin
set @count=@count+1
end
set @i=@i+1
end
select @count
go

EXEC charcount 'sqldeveloper','l' ---- give the inputs @a, @b

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-14 : 07:00:24

select len('sqldeveloper') - len(replace('sqldeveloper','l',''))

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

karthik0805
Starting Member

14 Posts

Posted - 2012-09-17 : 04:05:57

You can also find count of word in the sentence.
For example:

create proc charcount
@a varchar(8000),@b varchar(8000)
as
declare @i int = 1, @count int =0, @j int=0
select @j=LEN(@b)
while @i<=LEN(@a)
begin
if (select substring(@a,@i,@j))=@b
begin
set @count=@count+1
end
set @i=@i+1
end
select @count
go

EXEC charcount 'SQL DEVELOPERS ARE ONE OF THE BEST DEVELOPERS IN THE WORLD', 'DEVELOPERS'

The output is : 2

karthik0805
Starting Member

14 Posts

Posted - 2012-09-17 : 04:06:58

quote:
Originally posted by karthik0805

You can also find count of word in the sentence.
For example:

create proc charcount
@a varchar(8000),@b varchar(8000)
as
declare @i int = 1, @count int =0, @j int=0
select @j=LEN(@b)
while @i<=LEN(@a)
begin
if (select substring(@a,@i,@j))=@b
begin
set @count=@count+1
end
set @i=@i+1
end
select @count
go

EXEC charcount 'SQL DEVELOPERS ARE ONE OF THE BEST DEVELOPERS IN THE WORLD', 'DEVELOPERS'

The output is : 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-17 : 05:29:13

[code]declare @a varchar(max), @b varchar(max)
set @a = 'SQL DEVELOPERS ARE ONE OF THE BEST DEVELOPERS IN THE WORLD'
set @b = 'DEVELOPERS'

select (len(@a) - len(replace(@a,@b,''))) / len(@b)
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources