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?

  • Can you share your specific error? At what point in the process is this failing? – nbrown Mar 4 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 at 16:01
  • I would go ahead and add this as the answer and accept it. – nbrown Mar 4 at 16:18
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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.