Return to Answer

After some testing - you should be able to directly change from string to number without using CAST or CONVERT.

This worked with the strings "123","456","789". I was able to directly move them from a string field to a number field.

Your issue may lie with the commas - testing this now.

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

After some testing - you should be able to directly change from string to number without using CAST or CONVERT.

Your issue may lie with the commas - testing this now.

After some testing - you should be able to directly change from string to number without using CAST or CONVERT.

This worked with the strings "123","456","789". I was able to directly move them from a string field to a number field.

Your issue may lie with the commas - testing this now.

Possibly try

CAST ( Field1 AS INT )

This is an interesting usage of queries, but also begs the question, why is the initial data extension set up as a text field, when it seems like it should be an integer from the start.

You could also try exporting the data in the data extension (if you have Data Extracts), and reimporting that data into a different data extension. More on that topic here.

Let me know if this helps!

After some testing - you should be able to directly change from string to number without using CAST or CONVERT.

Your issue may lie with the commas - testing this now.