asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wasim Akram Shaik

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_aeo

ASKER

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

Wasim Akram Shaik

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_aeo

ASKER

show only one rows
JOHAN STIVE
and use TABLE ACCESS FULL