You can use cross apply and a custom split string function to achieve that.
CREATE FUNCTION [dbo].[SplitString]
(
@stringToSplit VARCHAR(MAX)
)
RETURNS @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos -1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) -@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
create table floormaster(floor char(10));
insert floormaster(floor) values('1,2,3,A,B');
insert floormaster(floor) values('5,7,3,A,B');
select max(Name) from floormaster f cross apply dbo.SplitString(f.floor) where ISNUMERIC(Name) = 1;
The result will be 7