> - 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!

Good evening,
 
the mechanics below works, you can turn it into a usable function in
your queries but it would be lighter to write a UDR.

execute block
returns (address varchar(100), city varchar(50))
as

declare variable NextPosR integer;
declare variable CurrentPosR integer;
declare variable FStr varchar(300);
begin
fstr ='- OLD HWY 11 / - OAK GROVE RD CITYNAME';
CurrentPosR = 1;
NextPosR = 0;
while (position(' ',:fstr , :CurrentPosR) > 0) do begin
NextPosR = position(' ', :fstr, CurrentPosR);
CurrentPosR = NextPosR+1;
end
:city = substring(:fstr from CurrentPosR for char_length(:fstr));
:address = substring(:fstr from 1 for
char_length(:fstr)-char_length(:city));
suspend;
end;

--
Norbert Saint Georges
http://tetrasys.fi