Note that new_addr_str will never end with a space, even if the original addr_str did.
SELECT RTRIM ( REPLACE ( addr_str || ' ' , ' CA ' , ' California ' ) ) AS new_addr_str FROM table_x ;
Edited by: Frank Kulash on Apr 1, 2013 3:31 PM
SELECT CASE WHEN SUBSTR (addr_str, -1) = ' ' THEN REPLACE ( addr_str , ' CA ' , ' California ' ) ELSE RTRIM ( REPLACE ( addr_str || ' ' , ' CA ' , ' California ' ) ) END AS new_addr_str FROM table_x ;
with xx as( select 'Redwood City, CA 96403' a from dual UNION ALL select 'CALDVIN Dr CA' a from dual UNION ALL select 'Blaca Street CA' a from dual UNION ALL select 'Redwood City, CA' a from dual ) select regexp_replace(a,'(\s)CA(\s)*','California') col from xx;
COL Redwood City,California96403 CALDVIN DrCalifornia Blaca StreetCalifornia Redwood City,California
WITH t AS (SELECT 'Redwood City CA ' Col FROM DUAL UNION ALL SELECT 'Redwood City, CA USA' Col FROM DUAL UNION ALL SELECT 'Redwood City CAUSA' Col FROM DUAL) SELECT CASE WHEN REGEXP_REPLACE (Col, '(.*) (CA)', '\2') IN ('CA','CA ') THEN col ELSE REGEXP_REPLACE (Col, 'CA', 'California') END AS ColR FROM T
it will make it 3461 CaliforniaDVIN Dr CaliforniaYou meant *3461 CaliforniaLDVIN Dr California* didn't you ? ;)
user6287828 wrote:It works when I do it.
Thanks but this does not work when CA is at the end. Because in that case CA does not have a space after it.
SELECT RTRIM ( REPLACE ( 'INCA TACATA CASA, CA' || ' ' , ' CA ' , ' California ' ) ) AS new_addr_str FROM dual;
Post your query, along with CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.
NEW_ADDR_STR ---------------------------- INCA TACATA CASA, California
user6287828 wrote:Regular expressions might not be the fastest way to do this, but the differenece might not be significant, or it might not justify the higher development and maintenance costs.
This is part of a bigger problem where I will have to check for only few different states like
if CA replace it to California
or if NY replace it with New York
so may be if this is doable with REGEXP_REPLACE
user6287828 wrote:In that case, nesting the functions one inside another won't be too bad. But watch out: this is exactly the kind of requirement that changes over time. You might only need 4 states now, nut next year they might expand the application so that you need all 50 states (plus Puerto Rico, and DC, and ....). In that case, something like MODEL, or a user-defined PL/SQL function, would be better.
... (only four of these abbreviations need to be replaced)
As these are abbreviations these will always be stand alone words and never part of words. And these abbreviation can be at the beginning or end or in between the string.Post CREATE TABLE and INSERT statements for the sample data.
And any abbreviation can be present in one column.
More than one abbreviation can be present in one column
None of the abbreviations can be present in this column.
3251 BLACA ROAD CA 94305
74 CALDWIN STREET CA
67 DIGITAL DRIVE NM
NM UNIVERSITY AVENUE 890
7645 ROCHESTER NY PARK STREET
834 GRAND AVENUE ATLANTA
WITH t AS (SELECT 'Redwood City, CA 96403' a FROM DUAL UNION ALL SELECT 'CALDVIN Dr CA' a FROM DUAL UNION ALL SELECT 'Blaca Street CA' a FROM DUAL UNION ALL SELECT 'Redwood City, CA' a FROM DUAL) SELECT trim(replace (REPLACE (a, ' CA ',' California '),' CA',' California ')) FROM t