Can't convert varchar values to data type int

Question

I have simple code. I need make easy operation: "column_A * 100 ", but I can't convert varchar type to int. My error is: Conversion failed when converting the varchar value '0.27' to data type int.

SELECT POWIERZCHNIA, 
POWIERZCHNIA * 100
FROM v_analiza_cechy_produktow

--Conversion failed when converting the varchar value '0,27' to data type int.

SELECT POWIERZCHNIA, 
CAST (REPLACE(POWIERZCHNIA, ',' , '.') AS int) 
FROM v_analiza_cechy_produktow

--Conversion failed when converting the varchar value '0.27' to data type int.

I want convert this values to int, but how?

---

@Larnu

SELECT POWIERZCHNIA FROM v_analiza_cechy_produktow 
WHERE TRY_CONVERT(decimal(10,4),POWIERZCHNIA) IS NULL;

TOP 20 rows:

NULL 1,08 0,21 0,85 1,38 0,00 2,88 3,00 2,40 1,00 1,36 0,30 2,16 3,24 NULL NULL NULL 2,88

Answer 1

You can't convert a varchar representation of a decimal directly to an int (SELECT CONVERT(int, '0.27'); will fail with the same error). You need to CONVERT to value first to a decimal and then an int. So, for your query:

SELECT POWIERZCHNIA,
       CONVERT(int,CONVERT(decimal(3,2),REPLACE(POWIERZCHNIA, ',', '.')))
FROM v_analiza_cechy_produktow;

Note that I have used decimal(3,2) as we only have one sample value '0.27'. you will likely need to choose a different scale and precision.

This does, however, ask the question; why are you storing decimal values as a varchar?

Answer 2

Do not understand why you want to replace , to . and then still cast as INT..., there is no point to do this. Below should work fine, please test it.

SELECT CAST(CAST('0.27' AS DECIMAL(5,2)) * 100 as int)

SELECT 
    POWIERZCHNIA, 
    CAST((CAST(POWIERZCHNIA AS DECIMAL(5,2)) * 100) as INT)
FROM v_analiza_cechy_produktow
WHERE POWIERZCHNIA IS NOT NULL

EDIT

It is basically the same answer as @Larnu, just used CAST instead of CONVERT...

EDIT

You can use ISNUMERIC function to check POWIERZCHNIA

SELECT 
    POWIERZCHNIA, 
    CAST((CAST(POWIERZCHNIA AS DECIMAL(5,2)) * 100) as INT)
FROM v_analiza_cechy_produktow
WHERE IsNumeric(POWIERZCHNIA) = 1