I have a function I created to remove characters and keep the number, but I have a problem with x/y values such as 1 1/2 where this would be 1.5. Currently I have this where it will do the percent part, but it will not include the first number... ie.. .5 instead of 1.5. How can I do this? Here is what I have...

CASE 
When (@input   ' ') like '%/%' and @input not like '% %'
    Then cast(cast(SUBSTRING ((@input   ' '), CHARINDEX('/',(@input   ' '))-1,1) as decimal (4,2)) 
            / cast(SUBSTRING ((@input   ' '), CHARINDEX('/',(@input   ' ')) 1,1) as decimal (4,2)) as float)
When @input not like '% %'
    Then @input   
END

CodePudding user response：

Another trick is to abuse dynamic sql to eval the equation.

Example:

DECLARE @input VARCHAR(10) = '1 1/2';
DECLARE @result DECIMAL(5,2);

DECLARE @sql NVARCHAR(100) = 'select @res=(' replace(@input,' ', ' 1.0*') ')';
DECLARE @params NVARCHAR(100) = N'@res DECIMAL(5,2) OUTPUT';

EXEC SP_EXECUTESQL @sql, @params, @result OUTPUT;
SELECT @result AS result;

db<>fiddle here