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

,

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

Rate

3.71 (7)

Share

Share

Rate

3.71 (7)