After some testing - you are able to directly change from string to number without using CAST or CONVERT. The data from the string field, must match the target data extension field, however.
If the field data type is NUMBER, your STRING must be:
- An integer
- You cannot import decimal values into a column with this data type. If you need to import decimal values, use the Decimal data type
instead
- This type accepts values from -2,147,483,648 to 2,147,483,648
This worked with the strings "123","456","789". I was able to directly move them from a string field to a number field using:
SELECT Field1 FROM data_extension
If the field data type is DECIMAL, your STRING must be:
- A number with a decimal point
- When you define a field as decimal, you indicate the total maximum length of the field and how many places after the decimal the field
can hold. For Length, the first field is the total maximum number of
digits and the second field is the maximum number of digits after the
decimal point.
I was able to make this work by using the following:
SELECT REPLACE(Field1,',','.') as 'Field1' FROM data_extension
If you truly don't care about the decimals, and want those to be integers, use something like this:
SELECT FLOOR(REPLACE(Field1,',','.')) as 'Field1' FROM data_extension
REPLACE(REPLACE(*NameOfColumn*,CHAR(10),'_'),CHAR(13),'_')) *NameOfColumn*
In the above form and in every line of your code, the above has an unmatched parens. There's an extra parens at the end of the above line.
I don't know anything about SFMC, but you appear to have an extra parentheses in each line of your code (the very last one). I recommend you try removing it to see if it fixes your issue.
EDIT:
My "gut" is telling me to suggest trying something like this:
REPLACE((REPLACE(CLIENTID,CHAR(10),'_'),CHAR(13),'_') CLIENTID AND
(REPLACE(SENDID,CHAR(10),'_'),CHAR(13),'_') SENDID AND
(REPLACE(SUBSCRIBERKEY,CHAR(10),'_'),CHAR(13),'_') SUBSCRIBERKEY AND
(REPLACE(EMAIL,CHAR(10),'_'),CHAR(13),'_') EMAIL AND
(REPLACE(LISTID,CHAR(10),'_'),CHAR(13),'_') LISTID AND
(REPLACE(EVENTDATE,CHAR(10),'_'),CHAR(13),'_') EVENTDATE AND
(REPLACE(EVENTTYPE,CHAR(10),'_'),CHAR(13),'_') EVENTTYPE AND
(REPLACE(BOUNCECATEGORY,CHAR(10),'_'),CHAR(13),'_') BOUNCECATEGORY AND
(REPLACE(SMTPCODE,CHAR(10),'_'),CHAR(13),'_') SMTPCODE AND
(REPLACE(BOUNCEREASON,CHAR(10),'_'),CHAR(13),'_') BOUNCEREASON AND
(REPLACE(BATCHID,CHAR(10),'_'),CHAR(13),'_') BATCHID AND
(REPLACE(TRIGGEREDSENDCUSTOMERKEY,CHAR(10),'_'),CHAR(13),'_') TRIGGEREDSENDCUSTOMERKEY AND
(REPLACE(PROMOID,CHAR(10),'_'),CHAR(13),'_') PROMOID)
The alternative being to also try removing the fieldname after the last parens (or even doing both the above and removing it after). For some reason, that seems unnecessary to me, but again, I'm not familiar with their code and haven't seen anything you may not have included in your post. It seems odd there would be nested REPLACE statements like that followed with the fieldname, first with options and then later without with more ANDs tacked on. It seems like there's potential to only need one outer replace statement. Its at least worth trying.
Best Answer
If you are looking at deleting Millions of rows then using a staging table may be the most optimized way, however for smaller volumes SSJS Activities may be able to help - for example using WSProxy (https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_WSProxy_delete.htm) would allow you to do all the same actions you would be able to do via SOAP - this would mean doing delete with primary keys Alternative would be using the deletedata function (https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_platformDataExtensionDeleteData.htm) which is purely value based.