Simple,

replace(reverse(substring(reverse(i.location) from position(' ', reverse(i.location)))), ' / - ', ' AT ' )

Though splitting on the last space would be a problem with cities having spaces in their names, e.g. New York (USA) or St Albans (England).

HTH,
Set

 

Hello All,

 

I could use some help/direction with splitting (or extracting data between certain characters in) an existing string.  This is generally a simple task in code using IndexOf, LastIndexOf, etc. but I can’t seem to figure out the syntax in Firebird SQL (and it has to be done in SQL unfortunately).  The string is stored in one field called “Location” and the results are being split into two columns (which are basically “ADDRESS” and “CITY”).   

 

Here is an example of the data:  202 CLARENCE RAY DR CITYNAME

 

So far I’ve written this SQL (might be a better way to do this): 

reverse(substring(reverse(i.location) from position(' ', reverse(i.location))))

 

Which returns:  202 CLARENCE RAY DR

 

And this SQL:

right(i.incident_location, position(' ', reverse(i.incident_location)))

 

Which returns:  CITYNAME

 

I thought that I had solved the problem…  Upon looking through the data for the “Location” field, I noticed that there appears to be intersections as well address information.  The intersections format is where I’m encountering the most trouble.  Here is an example of the intersection format:

 

- OLD HWY 11 / - OAK GROVE RD CITYNAME

 

I need to convert this data into a format like:   OLD HWY 11 AT OAK GROVE RD in one column and CITYNAME in a second column.  My right(i.incident_location, position(' ', reverse(i.incident_location))) should be sufficient for the CITYNAME, but I have no idea how to write the syntax to convert the first column. 

 

Any help would be greatly appreciated!