nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select records by number of spaces in field

Ona_Tim

I have a graves hosted feature layer that has Ownerfullname, Firstname, lastname, OccupantLabel, OccupantFirstName, OccupantLastName. Somewhere down the line some of the Ownerfirstname was placed in the OccupantFirstName which is different from the OccupantLabel. I cannot use a simple strip from the end of the string due to multiple last names and suffixes. I am looking for a way to select records from the OccupantLabel field by the number of spaces and haven't found a way. The field has been trimmed to remove any leading or ending spaces.

I have tried the following ways in the select by records window just to find 1 space:

OccupantLabel.Count(" ") = 1

OccupantLabel.Count(' ') = 1

(LEN(column1 + ';')-LEN(REPLACE(column1,' ','')) = 1)

OccupantLabel like '%" "%'

Any help would be apprciated.

Thank you.

Find more posts tagged with

Analysis

Accepted answers

kenbuja

If you have OccupantLastName field, subtract that from the OccupantLabel to get OccupantFirstName. Assuming that for 'John "Joe" Van Smith Jr', the OccupantLastName is 'Van Smith Jr'....

All comments

bbaker_tngeo

If I understand correctly, you're trying to remove 2 spaces from records, such that "BOB SMITH" (2 spaces between BOB and SMITH) would be "BOB SMITH". Is that correct?

If so, I have used Find and Replace within the attribute table in ArcGIS Pro to Find: <space bar x2> and Replace: <space bar x1>. No brackets or quotes are needed. You can test is first by just finding a record and verifying it's what you're looking for.

If you're wanting to find where a first name has been , could you construct a variable by concatenating the occupant Firstname + " " + lastname and then checking that against the OccupantLabel to see which records don't match?

Ona_Tim

Nope.

I have the following example

OccupantLablel OccupantFirstName(Exisiting) OccupantFirstName(What I need)

John Smith Alice John

John J Smith John John J

John "Joe" Van Smith Jr Joe John "Joe"

I am try to select records so I only select John Smith (one space) so I can strip the correct first name or select by 2 spaces in the case of John J Smith and so on.

DavidPike

Calculate Field, Python.

splitString = yourString.split()
oneSpace = splitString[0] + " " + splitString[1]

note - edited original post to use .split() default whitespace method which would be more robust than .split(" ") which would include >=2 consecutive whitespaces.

Ona_Tim

That would work for John J Smith but not for John Smith.

As John Smith would need to be

splitstring = yourstring.split(" ")

onespace = splitstring[0]

That is why I need to select the records base on the number of spaces before Calculating them.

kenbuja

If you have OccupantLastName field, subtract that from the OccupantLabel to get OccupantFirstName. Assuming that for 'John "Joe" Van Smith Jr', the OccupantLastName is 'Van Smith Jr'....

DavidPike

splitString would be a list which looks like this

['John', 'Smith']

splitString[0] would only return the first item 'John'. The code should work.

RhettZufelt

With leading/trailing spaces already stripped:

>>> s = 'John Smith'
>>> splitString = s.split(" ")
>>> splitString
['John', 'Smith']
>>> len(splitString)
2
>>> 
>>> 
>>> s = 'John J Smith'
>>> splitString = s.split(" ")
>>> splitString
['John', 'J', 'Smith']
>>> len(splitString)
3
>>>

You can also iterate through the split string and count the number of 'spaces'. this would be more robust if there could be more than one space in between owner names:

numSpaces = 0
for splstr in splitString:
	if splstr == '' or splstr is None:
		numSpaces +=1
print(numSpaces)

You can split the string by " " (space) and then see the length of the resultant string. Won't really help if there are multiple spaces in a row, but the len() will let you know if there is more than two components to the name.

Not sure if it works for you, but at least a coule ideas of "counting" spaces in the strings.

Ona_Tim

Thank you Ken! That was it. I used OccupantFirstname = !OccupantLabel!.rstrip(!OccupantLastName!)

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.