Converting VARCHAR with divide operand to INT-CodePudding

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

Is there a way to convert this VARCHAR to 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 system and that cannot be changed.

Appreciate all the help on this.

CodePudding user response：

You would have to use dynamic SQL... SQL Server does not support macro substitution

Example

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

Exec('Select ' @test )    -- 10

Exec('Select ' @test ' * 4' )  -- 40

CodePudding user response：

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

CodePudding user response：

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))