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.

[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.

[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.

[KenBuja · Accepted solution]

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'....

[Ona_Tim]

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

[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.

R_