String to Numeric Calculations

Question

Hello Everyone,

I have a string field that contains digits, for example "7-8", and I want to calculate it to new numeric field (double) as average "(7+8)/2" of the string value. Is it possible? The dash between integers seem to cause the problem.  Do you have any idea If It could be done?

Best wishes,

Öznur

stringtonumeric.PNG

abureaux_0-1723500829068.png

jcarlson · Answer

It won't be pretty, but using AGOL's subset of allowable SQL functions, you can try something like this:

((SUBSTRING(str_field, 1, (CHAR_LENGTH(str_field) - POSITION('-', str_field)))) + (SUBSTRING(str_field, (POSITION('-', str_field) + 1), 100))) / 2

I don't have an AGOL layer to fully test this on, but here's a DB Fiddle that's pretty close, though the POSITION function follows a different syntax:

https://www.db-fiddle.com/f/bJkJyB5dy4jNvVA5Qz6aHo/0

OznurGeze · Answer

Hi, I could try it in ArcGIS pro but not AGOL, because it says;

Thank you anyway 😞

Have a great day, too 🙂

JohannesLindner · Answer

With Arcade:

return Average(Split($feature.TextField, "-", -1, true))