create the new fields for city and zip.
<cfquery name="qCity" datasource="...">
Select city, id
from tbl
</cfquery>
<cfset regexp = "^(.+)[[:space:]]+([0-9]+)$">
<cfloop query="qCity">
<cfset stTmp = REFindNoCase(regexp,qCity.city,1,true)>
<cfif stTmp.pos[1]>
<cfset newcity =
Mid(qCity.city,stTmp.pos[2],stTmp.len[2])>
<cfset newzip =
Mid(qCity.city,stTmp.pos[3],stTmp.len[3])>
<cfelse>
<!--- format doesn't match --->
<cfset newcity = qCity.city>
<cfset newzip = "">
</cfif>
<cfquery datasource="...">
UPDATE tbl
SET newcity = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#newcity#">,
newzip = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#newzip#">
WHERE id = <cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#qCity.id#">
</cfquery>
</cfloop>
If you know that the zip is always there and has exactly 5 digits, you
can probably do it directly in access using this query:
UPDATE tbl
SET newcity = Left(city,Len(city)-6),
newzip = Right(city,5)
WHERE Len(city) > 6
> -----Original Message-----
> From: Bob Haroche [mailto:[EMAIL PROTECTED]
> Sent: maandag 16 februari 2004 22:03
> To: CF-Talk
> Subject: SOT: Splitting Access DB Columns
>
> My client gave me a copy of their "database" which has a
> field for city, containing both city and zip code, eg:
>
> New York 10021
> Santa Rosa 95404
>
> Within the DB I need to split this column into two, one for
> city and the other for zip.
>
> How can I do this using Access 2002 and/or ColdFusion and regexp.
>
> Thanks in advance.
>
> -------------
> Regards,
> Bob Haroche
> O n P o i n t S o l u t i o n s
> www.OnPointSolutions.com
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
