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
Best Answer
After some testing - you are able to directly change from string to number without using
CAST
orCONVERT
. 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:
This worked with the strings "123","456","789". I was able to directly move them from a string field to a number field using:
If the field data type is DECIMAL, your STRING must be:
I was able to make this work by using the following:
If you truly don't care about the decimals, and want those to be integers, use something like this: