This seems to be working on my test cases.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_itemCount]
(
@sList varchar(50),
@sValue varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @iPos int, @iNext int, @iRetCount varchar(50), @iValLen int
SELECT @iRetCount = 0
SELECT @iPos = 0
SELECT @iNext = 1
WHILE @iNext > 0
BEGIN
SELECT @iNext = charindex(',', @sList, @iPos + 1)
SELECT @iValLen = CASE
WHEN @iNext > 0
THEN @iNext
ELSE len(@sList) + 1
END - @iPos - 1
--PRINT convert(int, substring(@sList, @iPos + 1, @iValLen))
IF (rtrim(ltrim(substring(@sList, @iPos + 1, @iValLen)))=@sValue)
BEGIN
SELECT @iRetCount = @iRetCount + 1
END
SELECT @iPos = @iNext
END
RETURN @iRetCount
END
GO
select dbo.f_itemCount('A,V,C,P,AA,PP,R,RT,L,Z,Z,Z','AA')as ic
GO
Notice that I removed the spaces from the list.
J
-
Ninety percent of politicians give the other ten percent a bad reputation. -
Henry Kissinger
Politicians are people who, when they see light at the end of the tunnel, go
out and buy some more tunnel. - John Quinton
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:341288
Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
