Vfrdtyky

I am not able to convert '11-1' to INT in sql server, the result should be 10.
i tried with convert and cast but getting an error.

As has been discussed '11-1' can't be converted to an int because it's a varchar representing a numerical expression.

The only way to reliably derive an expression stored as a varchar is by using dynamic SQL, which, has it own problems; specifically SQL injection.

Assuming that your data is coming from a table, and that these varchar expressions only contain simple arithmetic, you could do something like this:

CREATE TABLE dbo.test (ID int IDENTITY(1,1),

                       varcharExpression varchar(20));

INSERT INTO dbo.test (varcharExpression)

VALUES('11-1'),('12*2'),('2+9'),('15/3'),

      ('2^4'), --not supported in this example. SQL Server doesn't use ^, it uses POWER: POWER(2,4)

      ('DROP TABLE dbo.test;'); --Intentional example of an injection attempt.



GO

DECLARE @SQL nvarchar(MAX);



SET @SQL = STUFF((SELECT NCHAR(10) + N'UNION ALL' + NCHAR(10) +

                         N'SELECT ' + CONVERT(varchar(6),ID) + N' AS ID,' + NCHAR(10) +

                         N'       ' + CASE WHEN varcharExpression NOT LIKE '%[^0-9-+*/]%' ESCAPE '' THEN varcharExpression ELSE N'NULL' END + N' AS ExpressionValue'

                  FROM dbo.test

                  ORDER BY ID ASC

                  FOR XML PATH(N'')),1,10,N'') + ';';

PRINT @SQL;

EXEC sp_executesql @SQL;

GO

DROP TABLE dbo.test

'11-1' is a string and can't be converted to INT. You can do that as

SELECT (CAST (11-1) AS INT)

Or by using Dynamic SQL as

DECLARE @SQL NVARCHAR(MAX) = N'SELECT 11-1';



EXECUTE sp_executesql @SQL;

Or even

CREATE TABLE Operations(

  ID INT IDENTITY(1, 1),

  Operation VARCHAR(45)

);



INSERT INTO Operations VALUES

('10-1'),

('10+1'),

('(5+5) - 5'),

('5+5');



DECLARE @SQL NVARCHAR(MAX) = N'SELECT ';



SELECT @SQL = @SQL + Operation + ' ,'

FROM Operations



SET @SQL = LEFT(@SQL, LEN(@SQL) - 1);



EXECUTE sp_executesql @SQL;

Or if you want to get it as rows

SELECT @SQL = @SQL + Operation + ' UNION ALL SELECT '

FROM Operations



SET @SQL = LEFT(@SQL, LEN(@SQL) - 17);



EXECUTE sp_executesql @SQL;

You can also declare the @SQL variable as follow if you want to name the column

DECLARE @SQL NVARCHAR(MAX) = N'SELECT Operations = ';

Demo

As a final note (or warning), be careful for SQL Injection if your column has other strings as it maybe malicious commands.

you can try like below, separate both part by using substring then
convert it into int then add both

SELECT convert(int, substring('11-1',0, charindex('-', '11-1')))+

convert(int, substring('11-1',charindex('-', '11-1'), len('11-1')))

It will return 10

Use dynamic SQL to parse expression

DECLARE @result int, @sql nvarchar(max);

SET @sql = N'SELECT ' + '11 - 1';

EXEC sp_executesql @sql

or

DECLARE @result int, @sql nvarchar(max);

SET @sql = N'SELECT @result = ' + '11 - 1';

EXEC sp_executesql @sql, N'@result int OUTPUT', @result = @result OUTPUT

SELECT @result

This will return 10

The example below shows how you could evaluate the T-SQL expression stored in a table column. However, I would advise against this approach unless the value is from a source that is fully trusted. Otherwise, there is a SQL injection risk.

Also, this method cannot be used easily to return values from multiple rows in a single result. You would need a cursor to evaluate and insert each result into a temp table/variable and then select from the table as the final result.

DECLARE @Foo TABLE(

      FooID int

    , Expression nvarchar(10) NOT NULL

    )

INSERT INTO @Foo VALUES(1, '11-1');

DECLARE @sql nvarchar(MAX) = N'SELECT ' + (SELECT Expression FROM @foo WHERE FooID = 1) + N' AS Result;';

EXECUTE (@sql);