Data extension field data type conversion by using sql

Question

Hi guys I have this data extension (name is just example):

extension1

Name | Data Type

Field1 | Text

All data inside of Field1 is number and there is no alfabet. My goal is to change this field data type into number. Since I can't change the data type directly on the data extension. I plan to capture all data in this field by using sql and put it into another field in another data extension with number data type.

so I write an sql query

SELECT CONVERT(INT,Field1) AS Field2 FROM extension1

and then I put the result in another data extension:

extension2

Name | Data Type

Field2 | Number

I got no error but i got no result either. please if anybody has any idea about this, please help. If my explanation is confusing please ask. I'll be happy to explain more. thanks

UPDATE: I forget to tell that values in Field1 contain comma(,) and there are variations to how many numbers are behind the comma

DATA EXAMPLE:

219,975

69,25

364,2

80

713,5333

that is all the variations

Answer 1

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