Casting new column to an INT after a case statement in SQL-CodePudding

I currently have the following SQL query which removes % symbols from a column and changes empty values to NULL

SELECT col,
CASE
    WHEN (REPLACE(col, '%', '') = '') THEN NULL
    ELSE REPLACE(col, '%', '')
    END AS newcol
    FROM test;

I'm now trying to get this to cast to an INT after this but I am getting Syntax errors, I tried to do so with the following code but I'm receiving syntax errors.

SELECT col,
       CASE WHEN (REPLACE(col, '%', '') = '') THEN NULL
            ELSE CAST(REPLACE(col, '%', '') AS INT)
       END AS newcol
FROM test;

But I am getting the following error with this code:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT)

The column "col" (varchar) is as follows

col
7%
_
9
60%

I want to have the following columns but as an int

colInt
7
Null
9
60

CodePudding user response：

Is this you're looking for?

SELECT col,
       CASE WHEN REPLACE(col, '%', '') = '' 
            THEN NULL
            ELSE CAST(REPLACE(col, '%', '') AS UNSIGNED)
       END AS newcol
FROM test;

You can also write it as follows:

SELECT col,
       CASE WHEN NOT REPLACE(col, '%', '') = '' 
            THEN CAST(REPLACE(col, '%', '') AS UNSIGNED)
       END AS newcol
FROM test;