I am trying to extracta the begining of a feild. The only this is that there is no set lenght on the characters that I am trying to extract. I am just trying to extract the city and seperate it from the state. This is some of the data that I am trying to do.
You need to have something consistent to look for in detrmining how far over to select for the part of the text string that you want. Because some cities are 2 words you can't use the blank space. I noticed that not every record has a comma after the city, otherwise you could use that. I only saw one consistency in your list and that was that all records were for Ontario. Is everything in Ontario?
If you want to build a query instead, you can build this into a field where 'Table1' is the table that your address information is currently in and 'FULL_ADDRESS' is the field that your current information is in: