| REGEXP_REPLACE for different name variations [message #639090] |
Wed, 01 July 2015 07:35  |
 |
madsen
Messages: 6
Registered: July 2014
|
Junior Member |
|
|
I am trying to use REGEXP_REPLACE to clean up the names of entities of any .INC,CO., LTD. etc.
I am using:
SELECT X, trim(REGEXP_REPLACE(upper(regexp_replace (X,'[^[:alnum:]'' '']', NULL)), '\INCORPORATED$|\INC$|\ LLC$|\ CORPORATION$|\LTD$|\INTL$|\SA CI$|\CO LTD$|\SA$|\LIMITED$|\SA DE CV$|\LTD$|\$|\LLC$|\BRANCH$|\ CO$|\CORP$|\ LLC CO B$|\PTE$'))
FROM my_data;
Is there a better way?
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP_REPLACE for different name variations [message #639095 is a reply to message #639090] |
Wed, 01 July 2015 08:23  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The only two methods that come to mine are regexp_replace, which you are already using, and nested replace statements. Usually, with something simple, replace is faster, but with more complex things regexp_replace is faster. You might try testing and running both and comparing.
I am curious why you are removing these official portions of the names. If it is to enable simpler searches, then there may be a better way to search, such as using Oracle Text, without removing the official parts of the names.
|
|
|
|
| Re: REGEXP_REPLACE for different name variations [message #639096 is a reply to message #639093] |
Wed, 01 July 2015 08:57 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Your query simply does not work:
SQL> SELECT X, trim(REGEXP_REPLACE(upper(regexp_replace (X,'[^[:alnum:]'' '']', NULL)), '\INCORPORATED$|\INC$|\ LLC$|\ CORPORATION$|\LTD$|\INTL$|\SA CI$|\CO LTD$|\SA$|\LIMITED$|\SA DE CV$|\LTD$|\$|\LLC$|\BRANCH$|\ CO$|\CORP$|\ LLC CO B
$|\PTE$'))
2 FROM my_data;
FROM my_data
*
ERROR at line 2:
ORA-00942: table or view does not exist
Quote:Everytime I ask a question people our trying to come up with a sarcastic answer like Michel Cadot
Maybe because each time you incorrectly post your question, maybe it would help if you rea and follow OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Note: With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
[Updated on: Wed, 01 July 2015 08:59] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
