Basic substr string parsing question

162
1
12-18-2019 03:42 PM
LisaSmith1
New Contributor II

I am using select_one that returns a string containing a  number (unknown number of digits) followed by a '-' followed by a text string. How do I use substr to parse out the number before the '-'.  It can be any number between 1 and 999.

For example "10-Lincoln Center Parkway" I just want to use the number 10 in pulldata call to look up additional information.

Thanks

0 Kudos
1 Reply
Jim-Moore
Esri Regular Contributor

Hi Lisa

One way to achieve this would be to use substr() with nested if statements. Luckily in this case you only have three scenarios (i.e. one-, two- or three-digit number) so the calculation shouldn't be too cumbersome! Check the postion of the "-" separator to work out how many digits, then return the first 1, 2 or 3 characters as applicable. For example:

if((substr(${address1},1,2)='-'),substr(${address1},0,1),if((substr(${address1},2,3)='-'),substr(${address1},0,2),if((substr(${address1},3,4)='-'),substr(${address1},0,3),'')))

Alternatively, you could use regular expressions in each if statement to match the pattern. For more info on regex please see: Formulas—Survey123 for ArcGIS | Documentation.

Hope this helps

Best,

Jim