> - 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
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
