What is the best way to get the following results from a field.

column says "02, 04, 05, 06, 08, 09"

the count I need in this case is 6

I've tried using a function to return the count from the field but it doesn't permit the use of using a select to return a value to the client.
here's how the code looks:

CREATE FUNCTION fnRunner(@Runners varchar(50))
RETURNS int -- count of runners
AS
BEGIN
    declare @Count int
         , @start int
    set @Count = 0
    set @start = 1

    while @start < @Runners
    begin
        select charindex(' ',@Runners,@start)

            set @start = charindex(' ',@Runners)
            set @Count = @Count + 1

    end

    return @Count


END
GO

Or this simple one line query...

DECLARE @Field VARCHAR(100)

SELECT @Field = '02, 04, 05, 06, 08, 09'

SELECT 1 + LEN(@Field) - LEN(REPLACE(@Field, ',', ''))

In you environment, that query will look like


SELECT YourColumn, 1 + LEN(YourColumn) - LEN(REPLACE(YourColumn, ',', ''))
FROM YourTable

not sure how that would help as I need the count of numbers in the field. (i.e. 6 = count(01 03 05 06 08 11))

Since the numbers are separated with commas, I just see the difference for the full string and the string with commas deleted. The difference in length is the number of commas deleted. Add 1 to that result and you get the count of numbers in the string.
Very basic.

This works even for the example you have given last, with spaces as delimiters.

SELECT YourColumn, 1 + LEN(YourColumn) - LEN(REPLACE(YourColumn, ' ', ''))
FROM YourTable

It looks like you might want:

SELECT Count(Table.Column) AS CountOfColumn
FROM Table;

This will turn this:

Column
02
04
05
06
08
08

Into this:

CountOfColumn
6


Is this what you want?



mmcdonal

The function Cole wrote above will work to, but due to a "feature" in SQL 2000, you must use dbo as owner.

select dbo.fnRunner('02, 04, 05, 06, 08, 09')