SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find the character's count in a word
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

karthik0805
Starting Member

14 Posts

Posted - 09/14/2012 :  06:46:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 09/14/2012 :  07:00:24  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 09/17/2012 :  04:05:57  Show Profile  Reply with Quote
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 - 09/17/2012 :  04:06:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 09/17/2012 :  05:29:13  Show Profile  Visit webfred's Homepage  Reply with Quote
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)



Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000