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.

  • 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? – Cameron Robert Oct 23 '19 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 '19 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.