Solved

REPLACE in a WHERE

Posted on 2014-04-25
5
1 solution
250 Views
Last Modified: 2014-04-30
Problem:
On the S_FIRST_NAME and S_LAST_NAME of table columns TABLE_CONTACT being saved in some cases two spaces between the names, for example:
S_FIRST_NAME = 'JOHAN  Stive'
S_FIRST_NAME = 'JOHAN Stive'
The query that is installed on production does not bring the right information, see query 2 and query 3

Query 2 (11 rows)
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE 'JOHAN STIVE%'
   AND TC.S_LAST_NAME LIKE 'TOLENTINO RAMIREZ%'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'

Query 3 (1 rows)
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE 'JOHAN  STIVE%'
   AND TC.S_LAST_NAME LIKE 'TOLENTINO RAMIREZ%'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'

Solution: The only one I've found is the query 1 with the use of REPLACE, but the problem is using a TABLE ACCESS FULL, there is some other way. The DBA will not allow the use of replace that causes a performance problem
query 1 (12 rows)
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND REPLACE(TC.S_FIRST_NAME, '  ', ' ') = 'JOHAN STIVE'
   AND REPLACE(TC.S_LAST_NAME, '  ', ' ') = 'TOLENTINO RAMIREZ'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'
query-1.jpg
query-2.jpg
query-3.jpg
Problema-REPLACE.docx
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40023149
if the REPLACE results in a FULL table scan, that's because the current index which includes the fields would need to include the same replace function call instead of the field itself.
this is called a function-based index.

you could also try:

 AND TC.S_FIRST_NAME LIKE 'JOHAN%STIVE%'
which translates into:
 AND TC.S_FIRST_NAME LIKE REPLACE( TRIM('JOHAN  STIVE%') , ' ', '%' )

the trim is to avoid that users putting leading spaces would result in a LIKE '%....%'

hope this helps
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40023462
Yes as Hengel suggests ,the best option would be to create a functional index on your column

however in case there are restrictions to do so, you can use your existing query itself and and add an instr function to the existing statement like this,

SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE 'JOHAN%'
   AND TC.S_LAST_NAME LIKE 'TOLENTINO%'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'
and instr(TC.S_FIRST_NAME,' ',1,2)>0
and instr(TC.S_LAST_NAME,' ',1,2)>0

amongst the existing results filter the results which have the second occurrence of space in any of the columns
0
 

Author Comment

by:enrique_aeo
ID: 40026176
This query it's work
SELECT TC.OBJID,
       TC.PHONE,
       TC.X_CONTACT_ADD2ADDRESS,
       TC.S_LAST_NAME,
       TC.S_FIRST_NAME,
       TC.X_CONTACT_STATUS,
       TC.X_DOC_TYPE,
       X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM TABLE_CONTACT TC, TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE REPLACE(TRIM('JOHAN STIVE%'), ' ', '%')
      AND TC.S_LAST_NAME LIKE REPLACE(TRIM('TOLENTINO RAMIREZ%'), ' ', '%')
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'

-- 2 rows
JOHAN  STIVE
JOHAN STIVE

this query it's not work
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM TABLE_CONTACT TC, TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE 'JOHAN%'
   AND TC.S_LAST_NAME LIKE 'TOLENTINO%'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'
and instr(TC.S_FIRST_NAME,' ',1,2)>0
and instr(TC.S_LAST_NAME,' ',1,2)>0
-- 0 rows
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40026609
its because of AND operator ,try this.. it should work

SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM TABLE_CONTACT TC, TABLE_CONTACT_ROLE CR, TABLE_SITE TS
 WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE 'JOHAN%'
   AND TC.S_LAST_NAME LIKE 'TOLENTINO%'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'
and( instr(TC.S_FIRST_NAME,' ',1,2)>0
or instr(TC.S_LAST_NAME,' ',1,2)>0)
0
 

Author Comment

by:enrique_aeo
ID: 40033392
show only one rows
JOHAN  STIVE
and use TABLE ACCESS FULL
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Learn More
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why does Oracle ignore my index?
Article by: sdstuber
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Five Myths of Oracle Database Backup
Article by: Acronis
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Taking User Managed Backups with Oracle
Video by: Steve
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Using Transportable Tablespace Functionality in Oracle
Video by: Steve
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Advertise Here
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here