Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

REPLACE in a WHERE

Posted on 2014-04-25
5
1 solution
Medium Priority
?
251 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 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Download free
Question has a verified solution.

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

Join types and their implementation using ANSI JOIN syntax in Oracle
Article by: Christoffer
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursor in Oracle
Article by: Swadhin
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Extract Information from the Oracle Data Dictionary Views
Video by: Steve
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

730 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