|
Subject:
|
getting a count of values from within a string
|
|
Posted By:
|
cole
|
Post Date:
|
8/22/2006 2:08:10 PM
|
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
|
|
Reply By:
|
cole
|
Reply Date:
|
8/22/2006 5:07:39 PM
|
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
|
|
Reply By:
|
Peso
|
Reply Date:
|
8/25/2006 12:28:27 AM
|
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
|
|
Reply By:
|
cole
|
Reply Date:
|
8/30/2006 11:39:16 PM
|
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))
|
|
Reply By:
|
Peso
|
Reply Date:
|
8/31/2006 1:45:48 AM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
9/5/2006 11:07:15 AM
|
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
|
|
Reply By:
|
Peso
|
Reply Date:
|
9/5/2006 11:20:37 AM
|
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')
|
