String to Numeric Calculations

113
3
09-28-2022 07:27 AM
Labels (2)
OznurGeze
New Contributor II

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

0 Kudos
3 Replies
JohannesLindner
MVP Regular Contributor

With Arcade:

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

Have a great day!
Johannes
0 Kudos
OznurGeze
New Contributor II

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

OznurGeze_0-1664376680153.png

Thank you anyway 😞

Have a great day, too 🙂 

0 Kudos
jcarlson
MVP Honored Contributor

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

jcarlson_0-1664381125186.png

 

- Josh Carlson
Kendall County GIS