Converting VARCHAR with divide operand to INT

Question

I have a VARCHAR which will return the value something like this '40/4'.

Is there a way to convert this VARCHAR to an INT? I tried casting but it is not working.

Sample:

Declare @test varchar(6) = '40/4'

Select cast (@test as int) * 4

Expected: 40

Actual:

Conversion failed when converting the varchar value '40/4' to data type int

Note: The value 40/4 is something coming from a message saved in one of the systems and that cannot be changed.

Appreciate any help on this.

Answer 1

CAST/CONVERT won't work because '40/4' is not a number, its a string that happens to represent a mathematical expression.

One way to solve this is by using dynamic SQL e.g.

declare @test varchar(6) = '40/4';

declare @sql nvarchar(max) = 'select ' + @test + ' * 4';

exec sp_executesql @sql;

Returns: 40

Answer 2

If you're just after the first valid integer value before the / (or just the first non-numeric character) you can try

Declare @test varchar(6) = '40/4'

Select Try_Convert(int,Left(@test,IsNull(NullIf(PatIndex('%[^0-9]%',@test),0),6)-1))