I need to match special character, how can I write query to get the required output?-CodePudding

I want mail which is common at supplier's and customer's tables only, I don't want emails that don't match in customer's table with the supplier's mail. Unfortunately, we don't have similar IDs to join so if possible can we join with the mail only, if not then we can consider common IDs and join them.

following tables for your reference and output that I want

CodePudding user response：

The proper solution is to normalize your database design to not store multiple values in the same column. However, I expect that is water under the bridge.

Given the data that you have, you can use STRING_SPLIT() to separate the emails from the SUPPLIERS table and then join with CUSTOMERS.

SELECT S.ID, C.EMAIL
FROM SUPPLIERS S
CROSS APPLY (
   SELECT value AS EMAIL
   FROM STRING_SPLIT(S.EMAIL, ',')
) E
JOIN CUSTOMERS C ON C.EMAIL = E.EMAIL

See this db<>fiddle

If your data may have any spaces mixed in, you may need to add TRIM() to the STRING_SPLIT() result - SELECT TRIM(value) AS EMAIL.

CodePudding user response：

    Select CC.ID, CC.email from
    (
            SELECT        ROW_NUMBER() OVER (ORDER BY ID ASC) AS AID,a.*
            FROM           Suppliers a
    ) as AA

    left outer join 
    (
            Select BB.* from
            (
                    SELECT        ROW_NUMBER() OVER (ORDER BY ID ASC) AS BID,b.*
                    FROM           Customers b
            ) as BB
    ) as CC
    on AA.AID = CC.BID