Converting a varchar value into an int fails when the value includes a decimal point to prevent loss of data.

If you convert to a decimal or float value first, then convert to int, the conversion works.

Either example below will return 7082:

SELECT CONVERT(int, CONVERT(decimal(12,7), '7082.7758172'));
SELECT CAST(CAST('7082.7758172' as float) as int);

Be aware that converting to a float value may result, in rare circumstances, in a loss of precision. I would tend towards using a decimal value, however you'll need to specify precision and scale values that make sense for the varchar data you're converting.

You can use the decimal data type and specify the precision to state how many digits are after the decimal point. So you could use decimal(28,20) for example, which would hold 28 digits with 20 of them after the decimal point.

Here's a SQL Fiddle, showing your data in decimal format.

Fiddle sample:

create table Table1(MyValues varchar(100))

insert into Table1(MyValues)
values                    
('-28.851540616246499'),
('-22.857142857142858'),
('-26.923076923076923'),
('76.19047619047619')

So the values are held as varchar in this table, but you can cast it to decimal as long as they are all valid values, like so:

select cast(MyValues as decimal(28,20)) as DecimalValues 
from table1

Your Sample

Looking at your sample update statement, you wouldn't be able to convert the values from varchar to a numeric type and insert them back in to the same column, as the column is of type varchar. You would be better off adding a new column with a numeric data type and updating that.

So if you had 2 columns:

create table Table1(MyValues varchar(100), DecimalValues decimal(28,20))

You could do the below to update the numeric column with the nvarchar values that have been cast to decimal:

update Table1
set DecimalValues = cast(MyValues as decimal(28,20))

I tried a lot of things to fix it, and no success.

I have a field called MASTER_BOL_NUMBER . According to documentation it is CHAR.

I see that inside it has only blanks and numbers

When I try to CAST( MASTER_BOL_NUMBER as numeric)

I am getting an error “Error converting data type varchar to numeric.”

I tried also smth like that

CAST( IIF(MASTER_BOL_NUMBER='',0,MASTER_BOL_NUMBER) as numeric)

and this

CAST( IIF(MASTER_BOL_NUMBER IS NULL,0,MASTER_BOL_NUMBER) as numeric)

Also no success, I really don’t understand why I am getting this error because usually CAST or CONVERT fix this data type issues.

Does someone else know what may help but for those functions?

If you don't mind rounding, you can do what you want in one step:

ALTER TABLE dbo.Stringy 
    ALTER COLUMN latitude decimal (9, 6) 
    WITH (ONLINE = ON); -- SQL Server 2016 onward

This can be an online operation in SQL Server 2016 or later.

If you need to truncate (not round) things are more tricky. You would need to perform a calculation like:

SELECT 
    CONVERT
    (
        decimal(9,6), 
        ROUND
        (
            CONVERT
            (
                decimal(10, 7), 
                S.latitude
            ), 
            6, -- decimal places
            1  -- truncate, don't round
        )
    )
FROM dbo.Stringy AS S;

If there might be some non-numeric values in there, use TRY_CONVERT instead and accept nulls in the new column, or provide a default value with ISNULL.

If the objective is to save space, you'll need to rebuild the table or run DBCC CLEANTABLE (perhaps in small batches until complete) to achieve that. The dropped variable-length string column will continue to take up space until then.

A conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. As to whether or not this actually happens depends on the order of evaluation in the execution plan.

Below is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated:

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'  

The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed:

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

Although the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. but it would be better to fix the data model such as to ensure only like or compatible data types are compared.