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.
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -