Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

REPLACE in a WHERE

Posted on 2014-04-25
5
Medium Priority
?
253 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
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

571 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