0

I have a data extension with a text field that contains numbers like 25.00, 130.00.

I'm trying to convert that to a number field so I can write a condition number greater then but because its a text field doesn't work

I looked for other topics related and tried cast, convert, replace but nothing seems to work

SELECT CAST(CAST (Address_ID AS NUMERIC(19,4)) AS INT) AS Address_ID FROM table_1 

SELECT CONVERT(INT,Address_ID) FORM table_1 

SELECT REPLACE(Field1,',','.') as 'Field1' FROM data_extension 

SELECT FLOOR(REPLACE(FIELD1,',','.')) as 'Field1' FROM [data_extension]

Any other ideas?

3
  • Can you share your specific error? At what point in the process is this failing?
    – nbrown
    Mar 4, 2020 at 15:59
  • 1
    just found the error, the solution is this SELECT REPLACE(Field1,',','.') as 'Field1' FROM data_extension Instead of the dot I had to write the exact decimal number that I set up the data extension with
    – Duicug
    Mar 4, 2020 at 16:01
  • I would go ahead and add this as the answer and accept it.
    – nbrown
    Mar 4, 2020 at 16:18

1 Answer 1

1

The solution is to use SELECT REPLACE(Field1,',','.') as 'Field1' FROM data_extension and Instead of the dot I had to write the exact decimal number that I set up the data extension with. So if your data type is 18,2 the query should look like this

SELECT REPLACE(Field1,',','2') as 'Field1' FROM data_extension

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .