Min and max value for columns
Info
These functions will return the minimum and maximum values within a concatenated text string.
To use this you will need to create the values that you need to parse in, and also have the TextToRows function as we will convert the string into rows and parse the relevant value back.
Originally only for Integers, we added a minimum and maximum for strings too.
With the string functions you may need to change the collation and change it to a top with an order by if you are looking for case sensitive.
SQL Code - MAX
CREATE FUNCTION [dbo].[GetStrMAX_INT](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS INT AS BEGIN
DECLARE @RS INT
SET @RS=(
SELECT MAX(CONVERT(INT,REPLACE(WordStr,@Delim,'')))
FROM Utilities.dbo.TextToRows(@Delim,@Str)
WHERE ISNUMERIC(WordStr)=1)
RETURN @RS
END
GO
SELECT dbo.GetStrMAX_INT(',','1,2,3,4,5,a')
SQL Code - MIN
CREATE FUNCTION [dbo].[GetStrMIN_INT](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS INT AS BEGIN
DECLARE @RS INT
SET @RS=(
SELECT MIN(CONVERT(INT,REPLACE(WordStr,@Delim,'')))
FROM Utilities.dbo.TextToRows(@Delim,@Str)
WHERE ISNUMERIC(WordStr)=1)
RETURN @RS
END
GO
SELECT dbo.GetStrMIN_INT(',','1,2,3,4,5,a')
We've got strings covered too...
ALTER FUNCTION [dbo].[GetStrMAX](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS NVARCHAR(100) AS BEGIN
DECLARE @RS NVARCHAR(100)
SET @RS=(
SELECT MAX(CONVERT(NVARCHAR(100),REPLACE(WordStr,@Delim,'')))
FROM Utilities.dbo.TextToRows(@Delim,@Str))
RETURN @RS
END
GO
ALTER FUNCTION [dbo].[GetStrMIN](@Delim NVARCHAR(1),@Str NVARCHAR(200)) RETURNS NVARCHAR(100) AS BEGIN
DECLARE @RS NVARCHAR(100)
SET @RS=(
SELECT MIN(CONVERT(NVARCHAR(100),REPLACE(WordStr,@Delim,'')))
FROM Utilities.dbo.TextToRows(@Delim,@Str))
RETURN @RS
END
GO
Testing
SELECT dbo.GetStrMAX_INT(',','1,2,3,x,5,a') --5
SELECT dbo.GetStrMIN_INT(',','1,2,3,x,5,a') --1
SELECT dbo.GetStrMAX(',','1,2,3,x,5,a') --x
SELECT dbo.GetStrMIN(',','1,2,3,x,5,a') --1
