splitting values and compare with tables using SQL Server Join

Ram

I have two tables called Components and PDF_MSDS. The Component table contains columns f_chem_name, f_component_id and f_chem_name and it contains below sample data.

Components table

SELECT F_Cas_Number, F_Component_Id, F_Chem_Name 
FROM Components  
WHERE F_Chem_Name = 'CHMNM_17816'

OUTPUT

F_Cas_Number    F_Component_Id  F_Chem_Name
-------------------------------------------        
CAS_5861         PRD1000826      CHMNM_17816

Sample data of PDF_MSDS table

F_PRODUCT    F_CAS_NUMBERS                         F_COMPONENT_IDS
----------------------------------------------------------------------
360          CAS_5779¿CAS_5861¿CAS_2614¿           3E000685¿3E002268¿3E004960¿3E005217¿PRD1000826¿

Now I want display the f_product value from the PDF_MSDS table by comparing F_Cas_Number and F_Component_Id in Components table with F_CAS_NUMBERS and F_COMPONENT_IDS in the PDF_MSDS table for the given f_chem_name in Components table.

But I am not able compare directly using join with F_Cas_Number in Components table with F_CAS_NUMBERS in PDF_MSDS table and also F_Component_Id in Components table with F_COMPONENT_IDS in PDF_MSDS because multiple cas_numbers and component_ids are stored in F_CAS_NUMBERS and F_COMPONENT_IDS columns in PDF_MSDS table.

How can I search and compare cas_numbers and component_ids and select particular record.Please help.F_Chem_Name is input parameter for Procedure.

Tab Alleman

You can use LIKE in your JOIN conditions.

JOIN TableB
  ON TableA.Column LIKE '%'+TableB.Column+'%'

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related