How can I possibly get the state out?

Question

I'm facing with a strange records. 
I need to get the state information out from 4 column
NameAddresses. 
Here is the scenario:
Column NameAdd1 is populated with records like: 
1234 Smith Avenue TX 77581-5021, 
3625 Cricket Creek DR,
555 RALPH MCGILL BLVD NE (NE is not Nebraska, NE stands for
North East)

Column NameAdd2: 
blank, >>>> because the State information for
this person has been placed in NameAdd1 TX 77581-5021
DELTONA FL 32725, >>> this is the continuation from
3625 Cricket Creek Dr address on NameAdd1
RIO RANCHO NM 87144 >>is a continuation from 555 RALPH
MCGILL BLVD NE

Column NameAdd3 : 
blank, 
blank, 
blank, 
MILWAUKEE WI 53202, 
16 Pueblo Drive

Column NameAdd4: 
blank,
blank,
blank,
blank, 
Alhambra CA 91207

So, when Column NameAdd1 has contain State abbriviation and
zip code (xxxxx-xxxx OR xxxxx) then other column NameAdds will be
blank, when ColumnNameAdd1 only contain street name, then the state
for that address is found on the next column which is NameAdd2, the
same thing happen when NameAdd1 and nameAdd2 only contain person
name and stree address then NameAdd3 or nameAdd4 will pick up the
State information and zip code for this person address, etc

What technique could possibly I use to get only the state
from each record from these 4 NameAdd columns? can anyone offer any
thought?

Dan_Bracuk · Answer

If the blank fields are null, then the concatonation will be null also. If they are already empty strings, you should be fine.

Char(0) is an empty string. Spaces are char(32). Plus, with the quotes and the dollar sign, what do you think the value of your MySpace variable actually is.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded