i am struggling with getting my query to include desired data, basicly i want to JOIN tag with document when the query finds a match with TAG and if document title in CLIENT_Document contains the tag_no. its working perfectly when it finds an exact match however some of the data in TAG column is prefixed with 60 and is exluding that data. i have tried making a case statement with substr or regex replace but i havent been able to make it work. Would appreciate help from anyone , attached is a picture that shows desired data from tables and result/ desired result
tag.project_id, tag.tag_no, tag.fac_suffix, tag.tag_suffix , technical_document.project_no AS "PROJ_DOC_PROJ_NO", technical_document.doc_no AS "PROJ_DOC_DOC_NO", client_document.doc_title, tag.func_domain_id FROM technical_document, tag JOIN client_document ON client_document.doc_title LIKE '%'||TAG_NO||'%' WHERE tag.project_no LIKE '%029P%' AND client_document.doc_no LIKE '%'||technical_document.doc_no||'%'