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!
