fro

/*
	UDF_LISTLEN
	==================================================
	Created on 02/19/2008 by Robert Froehling
		- returns the length of a list
		- this was modified from count string function at the link below
		- http://www.sql-server-helper.com/functions/count-string.aspx
*/

CREATE FUNCTION UDF_LISTLEN(
	@list VARCHAR(8000),
	@delim VARCHAR(5))
RETURNS TINYINT
BEGIN
	-- define the vars to use
	DECLARE @modString VARCHAR(8000)
	DECLARE @newString VARCHAR(8000)
	DECLARE @oldStringLen SMALLINT
	DECLARE @modStringLen SMALLINT
	DECLARE @newStringLen SMALLINT
	DECLARE @delimLen TINYINT
	DECLARE @diff TINYINT

	-- set some default vars
	SET @oldStringLen = LEN(@list)
	SET @delimLen = LEN(@delim)
	SET @modString = @list
	SET @modStringLen = LEN(@modString)

	-- if the first part of the string is the delim then remove it
	IF SUBSTRING(@modString, 1, @delimLen) = @delim
	BEGIN
		SET @modString = SUBSTRING(@modString, @delimLen + 1, @oldStringLen - @delimLen)
		SET @modStringLen = LEN(@modString)
	END

	-- if the last part of the string is the delim then remove it
	IF SUBSTRING(@modString, @modStringLen - @delimLen + 1, @delimLen) = @delim
	BEGIN
		SET @modString = SUBSTRING(@modString, 1, @modStringLen - @delimLen)
		SET @modStringLen = LEN(@modString)
	END

	-- count the lengths and setup the diff to return
	SET @newString = REPLACE(@list, @delim, '')
	SET @newStringLen = LEN(@newString)
	SET @diff = @modStringLen - @newStringLen + 1

	RETURN @diff
END

DECLARE @list VARCHAR(500)
DECLARE @delim VARCHAR(1)
SET @list = 'fro,ev,jc,ds'
SET @delim = ','
PRINT master.dbo.udf_listlen(@list, @delim)