Steps to Convert nvarchar to int Impeccably in SQL Server

Mostly many of the users of SQL database faces problems when they need to convert  nvarchar to int. In the SQL Server database there is no provision or no option is available to execute such task. But in this section we provide you a simple way by which you can convert nvarchar  to Int . For this process, you need to follow few steps which help to convert nvarchar to int.

Suppose you have an amount column lock

30,000.00
-74,000.00
45,000.00

It's data type is nvarchar.

When you try to convert this into Int, it does not work. If you used  to replace to get rid of ','.
And when I check the values using is numeric, every record returns 1.  In order to resolve this try converting to decimal and then to end.Or trimming off the .00 at the end of each string.  Try the below queries which helps you to convert nvarchar to int

Converting first twodecimals and then that decimal to integer for example:

DECLARE @Numb AS VARCHAR(50)

SET @Numb = '-45,000.00'

SELECT CAST(CAST(REPLACE(@Numb,',','') AS DECIMAL(10,2)) AS INT)

But be careful as:

DECLARE @Numb AS VARCHAR(50)

SET @Numb = '-45,000.99'

SELECT CAST(CAST(REPLACE(@Numb,',','') AS DECIMAL(10,2)) AS INT)

Truncates the input value, that is -45,000.00 and -45,000.99 both return an integer value of -74000

Here are some other  options which helps in conversion .

• CBool—Converts a value to a Boolean data type.
• CDate—Converts a value to a Date data type.
• CInt—Converts a value to an Integer data type.
• CStr—Converts a value to a String data type.
• CVar—Converts a value to a Variant data type.

Syntax

• CBool(expression)
• CByte(expression)
• CCur(expression)
• CDate(expression)
• CDbl(expression)
• CDec(expression)
• CInt(expression)
• CLng(expression)
• CSng(expression)
• CStr(expression)
• CVar(expression)

Converting to an Integer Data Type

TheCInt function takes a numeric or string value and converts it to an Integer data type. The argument is required and needs to represent a value within the range of -32,678 to 32,767. If the argument contains a decimal, Access rounds to the next whole number. A value of .5 or higher is rounded up; anything lower is rounded down. Some examples of CInt functions follow:

Dim MyDouble, MyInt

MyDouble = 2345.5678    ' MyDouble is a Double.

MyInt = CInt(MyDouble)    ' MyInt contains 2346.

IN SQL, you could use CAST.

SELECT

CAST([Column] AS INT) AS [Column Name]

FROM [Table]

WHERE ...

Or, if you wanted to SUM off the SQL query.

SUM(CAST([Column] AS INT)) AS [Column Name]

The above methods are sophisticated to use for covertingnvarchar to int as well as other data types.

Perhaps you should store the data in SQL Server as an INT field, instead of a VARCHAR(255) field?

If you did that, you wouldn't need to make any conversions in Access.

Having said that, if you don't want to make changes to the SQL Server database, you can use VB's CInt(field) in Access queries to convert numbers stored as a string into integers. Be aware this approach will fail if there are any non-numeric values stored in the varchar field.

I don't want to make any changes from SQL side.Only from access side