Find maximum value by passing comma separated integers as string in stored procedure

Question

String = '45,12,37,89,10,107,64,97'

Create a procedure that takes comma separated values, we can say array of numbers and find maximum of those values.

SQL Server has types **designed** for holding multiple values (tables, XML, JSON). It's also got very weak string processing functionality. So why are you setting yourself the challenge of using the wrong type? — Damien_The_Unbeliever, Oct 18 '17 at 08:54
Your question is really asking how to get the CSV data into separate rows, after which point you just need a `SELECT MAX` to answer your question. But, this has been answered already on Stack Overflow. — Tim Biegeleisen, Oct 18 '17 at 08:55

score 0 · Answer 1 · answered Oct 18 '17 at 09:01

You can do as the following.

Create a function to extract data

CREATE FUNCTION [dbo].[fnSplitString]
(
      @str as varchar(max),
      @delimiter as varchar(1)
)
RETURNS @TableReturn table(ID integer)
AS
BEGIN
    IF (@str IS NULL or @str ='')
        BEGIN
              INSERT INTO @TableReturn SELECT 0
        END
    ELSE
        WHILE (charindex(@delimiter,@str)>0)
        BEGIN
            INSERT INTO @TableReturn
            SELECT SUBSTRING (@str,1,charindex(@delimiter,@str)-1)
            SET @str = SUBSTRING (@str,charindex(@delimiter,@str)+1, len(@str))
        END
        IF (@str <> '' and @str <> ',' )
        BEGIN
            INSERT INTO @TableReturn SELECT @str
        END
    RETURN 
END

And then call it :

SELECT TOP(1)* FROM dbo.fnSplitString('45,12,37,89,10,107,64,97', ',') ORDER BY ID DESC

Or else :

SELECT MAX(ID) FROM dbo.fnSplitString('45,12,37,89,10,107,64,97', ',')

score 0 · Answer 2 · answered Oct 18 '17 at 09:20

I have this method bookmarked. Don't remember the source, but it works like charm. Have modified it for you. Try :

Create table #Testdata(Data varchar(max))
Insert #Testdata select '45,12,37,89,10,107,64'


;with tmp(DataItem, Data) as (
select LEFT(Data, CHARINDEX(',',Data+',')-1),
       STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from #Testdata
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
       STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select  max(cast(DataItem as int))
from tmp
OPTION (maxrecursion 0)

Find maximum value by passing comma separated integers as string in stored procedure

2 Answers2