I have a field which combines the City, State and zip of an address as
follows:

WOODSIDE, NY  11377

Im trying to separate this one field into 3.
Note:
The delimeters are always the same as above but the zip might sometimes be 6
characters

Ben,

I assume you want to use this in an SQL query. The simplest way would be to write an EDB function. If you are not using Unicode, then remove the "UNI" below.

Create FUNCTION "SplitCityStateZip" (IN "aCityStateZip" VARCHAR(255) COLLATE UNI_CI, IN "aElement" VarChar(1) COLLATE UNI_CI)
RETURNS VARCHAR(255) COLLATE UNI_CI
BEGIN

 --Declare aCityStateZip VarChar(255) default 'WOODSIDE, NY  11377';
 --Declare aElement      VarChar(255) default 'z';

 Declare _Result  VarChar(255);
 Declare _Str     VarChar(255);
 Declare _Element VarChar(1);
 Declare _P       Integer;

 set _Element = Upper(Trim(both ' ' from aElement));
 set _Str     = Trim(both ' ' from aCityStateZip);
 set _p       = Pos(',', _Str);

 if _Element='C' then
   set _Result = Trim(both ' ' from SubStr(_Str,1,_p-1));
 else
   set _Str = LTrim(SubStr(_Str, _p+1));
   set _p = Pos(' ', _Str);
   if _Element='S' then
     set _Result = SubStr(_Str,1,_p-1);
   else if _Element='Z' then
     set _Result = Trim(Both ' ' from SubStr(_Str,_p+1));
   end if;
   end if;
 end if;

 --set Log Message to _Result;
 Return _Result;
END

To use the function, you will need to call it 3 times if you want all 3 fields parsed from it because a function can only return one value at a time. You pass it the column name or expression that contains the "City, State Zip" and the 2nd parameter tells it what to extract. Use "c" to extract the City, 's' to extract the State, or 'z' to extract the zip code.

Select SplitCityStateZip(CityStateZip,'c') as City, SplitCityStateZip(CityStateZip,'s') as State, SplitCityStateZip(CityStateZip,'z') as Zip from MyTable;

will give you the 3 columns, City, State, and Zip.

Barry