Occurs function ?

[fluteplr]

Is there a an function in SQL server, or some other trick that would return the number of times a particular string occurs within another string.

For example the field bigstring = 'AAABCDDD'

occurs('A',bigstring)=3
occurs('ABC',bigstring)=1

thanks

 

[Andel]

You can create your own function like this...


CREATE FUNCTION Occurs (
@findstr varchar(100),
@mystr varchar(100)
)
RETURNS int
AS
BEGIN
DECLARE @cnt int
SET @cnt = 0
WHILE CHARINDEX(@findstr, @mystr, 1) <> 0
BEGIN
SET @cnt = @cnt + 1
SET @mystr = SUBSTRING(@mystr, CHARINDEX(@findstr, @mystr, 1) + 1, 100)
END
RETURN(@cnt)
END


And then execute it like this...

SELECT dbccurs('A', 'AAABCDDD')


Andel
andelbarroga@hotmail.com

http://andyb.8m.com