1

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.

2
  • Hi Nix9247, can you confirm there is no alphabet characters in the Address_ID column? The example you provide is a number stored as text, however could there be NULL or "alpha-numeric" values also? Oct 23, 2019 at 13:05
  • Cameron, I've used this one and it worked (plus I remove the non-nullable constraint), but I needed to do the NUMERIC conversion first and I don't know what are the numbered parameters in the parenthesis. Can you please provide an explanation so I know for the future uses? SELECT CAST(CAST (Address_ID AS NUMERIC(19,4)) AS INT) AS Address_ID FROM table_1
    – nix9247
    Oct 23, 2019 at 13:25

1 Answer 1

1

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...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .