[SalesForce] Marketing Cloud: Convert STRING to INTEGER(NUMBER) using SQL

We would like to do a SQL query on a master data extension from which there is a data column which was set as "Text" instead of "Number". We would like to make a conversion into the target DE from string to integer. However, we get a "query failed during execution – Conversion failed when converting the nvarchar value to data type int" error. Here's the portion of the SQL in Automation Studio referring to the columnSELECT CONVERT(INT,Address_ID) FORM table_1. We also tried with CAST, namely: SELECT CAST(Address_ID AS INT) AS Address_ID FROM table_1" but without any results. An example of a record is: 389085. Please help.

Best Answer

it worked for me this way:

SELECT CONVERT(FLOAT,A.Field) AS IntField FROM A

having IntField field type of target data extension = Number. You had better to insert also a control of null or empty values and a trim to delete blank spaces before conversion: SELECT...

CASE 
    WHEN A.Field IS NOT NULL AND 
        RTRIM(LTRIM(A.Field))<>'' THEN
        CONVERT(FLOAT,LTRIM(RTRIM(A.Field)))
    ELSE NULL
END AS IntField ...

FROM...

Related Topic