SQL Function to get Max, Min values from given collection of values

erichowell, 2011-01-20 (first published: 2011-01-08)

The 2 parameters in function are

Collection of values separated by specified separator(the separator defined in second parameter)

The second parameter is separator of collection values.

The function returns

The max value in the collection, min value in the collection and number of values in the collection.

For example:

if we run this statement

select * from dbo.GetMaxandMinValue('2,22,5,1,88,2000,7897,800',',')

The result will be:

minValue maxValue CountOfNumberSupplied
--------------------------------------- ----------------------------
1.00 7897.00 8

Create function GetMaxandMinValue(@ValuesCollection varchar(max),@separator char(1)) Returns @table Table(minValue numeric(20,2),maxValue numeric(20,2),CountOfNumberSupplied int ) AS Begin declare @x xml declare @minNumber varchar(50) declare @maxNumber varchar(50) declare @numberCount varchar(20) select @x='<number>' + replace(@ValuesCollection,@separator,'</number><number>')+ '</number>' select @minNumber= convert(varchar(50),@x.query('fn:min(number)') ) select @maxNumber= convert(varchar(50),@x.query('fn:max(number)')) select @numberCount= convert(varchar(50),@x.query('fn:count(number)')) insert into @table select cast(@minNumber as float),cast(@maxNumber as float),cast(@numberCount as int) Return; END