Skip to main content

The VertiGIS Community has been moved to the VertiGIS Support Center >>

calculated field

Marion Bishop

In a report I have an address field with this format: 
400 Queen Street
24 Bridge Street 
etc.
I want to order my report based on Street name, not number. I was thinking to make a calculated field, split out the address and choose the second item. I was messing around with things like ElementAt() but haven't had success. 
Any suggestions?
Thanks!

0

Comments

2 comments

Date Votes
  • Official comment
    Patrick Fingler

    Hi Marion,
    This should be possible, but there could be issues if you have some addresses that don't have a number in front of them if you do a split on the first space.  As can be seen in the example below, I can sort by a calculated field that extracts the street name but if a street doesn't have a number it ends up taking the suffix (Rd, Dr, St etc) - Ex: Greenlake Dr
    The calculated field is:
    Substring([PSTLADDRESS], CharIndex(' ',[PSTLADDRESS]) + 1, Len([PSTLADDRESS]) - (CharIndex(' ',[PSTLADDRESS])+1))

    Hope that helps!

  • Marion Bishop

    Hi Patrick,

    That worked perfectly. Thank you for that. I believe there are numbers for every address in my dataset, so this method works out well.

    Thanks!

    0