Status: Solved
Priority: Medium
Security: Public
Views: 257
Last Modified: 2014-04-30

REPLACE in a WHERE

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

enrique_aeo

Asked: 2014-04-25

Advertise Here

Oracle Database

1 Solution

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2014-04-25

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

LVL 16

Wasim Akram ShaikCommented: 2014-04-25

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

enrique_aeoAuthor Commented: 2014-04-27

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

LVL 16

Wasim Akram ShaikCommented: 2014-04-27

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)

enrique_aeoAuthor Commented: 2014-04-30

show only one rows
JOHAN STIVE
and use TABLE ACCESS FULL

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

REPLACE in a WHERE

Join & Write a Comment Already a member? Login.

Featured Post