String function - no of occurances

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

Transact-SQL (2000)

String function - no of occurances

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2003-11-25 : 01:04:28

Is there a built in function in sql to find the number of occurances?
for e.g.
main string - 'abracadabra'
substring -'a'

i need a function which will return - 5

nathan_d_s
Starting Member

4 Posts

Posted - 2003-11-25 : 01:35:48

I do not think such a function is available in SQL. But you can very well create a function of your own. The function is as given below:-

Create Function dbo.SubStringCount (@MainString varchar(500),@SubString varchar(100))
Returns int
As
Begin
Declare @varTemp varchar(500),@count int, @index int
Set @varTemp=@SubString
Set @index=1
Set @count=0
While(@index>0)
Begin
Select @index = PatIndex('%'+@varTemp+'%',@MainString)
Set @varTemp = @varTemp + '%' + @SubString
Set @count = @count + 1
End
Return @count-1
End

Use it like

Select dbo.SubStringCount('abracadabra','a')

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-25 : 01:55:16

An alternative approach...this might be faster for a larger number of rows:


DECLARE @SearchString VARCHAR(500)
DECLARE @Find VARCHAR(5)

SET @SearchString = 'abracadabra'
SET @Find = 'a'

SELECT LEN(@SearchString) - LEN(Replace(@SearchString, @Find, ''))

Owais

Please excuse my driving, I am reloading.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2003-11-25 : 02:25:57

That's beautiful !!
Great scripting.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2003-11-25 : 04:07:28

-- Your code will not work for a string like 'abracadabra a'
-- I have modified the script to take care the above situation
-- and also for substring having more than one Character

DECLARE @SearchString VARCHAR(500)
DECLARE @Find VARCHAR(5)
DECLARE @noo INT -- number of Occurrences

-- If the string which needs to be searched is present at the left most or rightmost part
-- of the main string, after replacement the string gets truncated.
-- The '?' has been added to avoid this problem

SET @SearchString = '?'+'abracadabra a' +'?'
SET @Find = 'a'

SELECT @noo=LEN(@SearchString) - LEN(Replace(@SearchString, @Find, ''))
select @noo=@noo/len(@Find)
select @noo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources