USE Northwind
GO

CREATE TABLE bkTable(col1 varchar(255))
GO

INSERT INTO bkTable(col1)
SELECT 'LIEB BLK PITTED OLIVE 19.75 OZEB' UNION ALL 	-- I would need the 19.75 
SELECT '+GRGNT YLW FSONN N/AV 80OZ' UNION ALL 		-- I would need the 80 
SELECT 'HI GINGR PWDR 2.25 OZ GNGR PWDR ' UNION ALL 	-- I would need the 2.25 
SELECT '+SARL1 SHPDR BRD N/AV N/AV 24OZ' 		-- I would need the 24 
GO


CREATE FUNCTION udf_LAST_SPACE
	(@str varchar(8000))
RETURNS int
AS
BEGIN
	DECLARE @Words INT, @Pos INT, @x Int, @y Int
	SELECT @Words = 0, @Pos = 1, @x = -1

	WHILE (@x <> 0)
		BEGIN
			SET @y = @x
			SET @x = CHARINDEX(' ', @str, @Pos)
			SET @Pos = @x + 1
			SET @Words = @Words + 1
		END

	RETURN @y
END
GO

SELECT   CONVERT(varchar(15),RIGHT(SUBSTRING(col1,1,CHARINDEX('OZ',col1)-1),
	  LEN(RTRIM(SUBSTRING(col1,1,CHARINDEX('OZ',col1)-1)))
	 - dbo.udf_LAST_SPACE(RTRIM(SUBSTRING(col1,1,CHARINDEX('OZ',col1)-1)))
	))
	, SUBSTRING(col1,1,CHARINDEX('OZ',col1)-1)
FROM bkTable
GO

DROP FUNCTION udf_LAST_SPACE
GO

CREATE FUNCTION udf_OZ
	(@str varchar(8000))
RETURNS varchar(15)
AS
BEGIN
	DECLARE @xxx as varchar(15)
	SELECT   @xxx = CONVERT(varchar(15),RIGHT(SUBSTRING(@str,1,CHARINDEX('OZ',@str)-1),
		  LEN(RTRIM(SUBSTRING(@str,1,CHARINDEX('OZ',@str)-1)))
	 	- dbo.udf_LAST_SPACE(RTRIM(SUBSTRING(@str,1,CHARINDEX('OZ',@str)-1)))
		))

Return @xxx
END
GO

SELECT dbo.udf_OZ(col1) FROM bkTable

DROP FUNCTION dbo.udf_OZ
GO