Subject Re: [firebird-support] Help with splitting a string in Firebird SQL
Author Svein Erling Tysvær

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


ons. 9. okt. 2019 kl. 00:14 skrev Ron Pillar Ron.Pillar@... [firebird-support] <>:

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 returnsCITYNAME


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:




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!