March 8, 2016 at 7:43 am
Hi,
CREATE TABLE #TableSol
(
ID INT IDENTITY(1,1),
Text VARCHAR(100)
)
INSERT INTO #TableSol ( Text)
SELECT 'ZUS_SDD_Intercompany Invoicing' UNION ALL
SELECT 'ZUS_SDD_eInvoicing' UNION ALL
SELECT 'ZUS_SDD_Change/Cancel Invoice'
SELECT 'ABCvvv'
CREATE TABLE #Table2
(
ID INT IDENTITY(1,1),
Deliv VARCHAR(500)
)
INSERT INTO #Table2 (Deliv)
SELECT ';#ZUS_SDD_Intercompany Invoicing;#ZUS_SDD_Intercompany Invoicing;#ZUS_SDD_Intercompany Invoicing;#' UNION ALL
SELECT 'ZUS_SDD_eInvoicing' UNION ALL
SELECT '####ZUS_SDD_Change/Cancel Invoice####'
SELECT * FROM #Table2
SELECT * FROM #TableSol
--Desired result using contains in the join to join two tables on Deliv and TExt. If Text is contained in Deliv column then Yes else no
SELECT 'ZUS_SDD_Intercompany Invoicing' AS Text ,'Yes' AS IsContains UNION ALL
SELECT 'ZUS_SDD_eInvoicing' AS Text,'Yes' AS IsContains UNION ALL
SELECT 'ZUS_SDD_Change/Cancel Invoice' AS Text,'Yes' AS IsContains UNION ALL
SELECT 'ABCvvv' AS Text ,'No'
DROP TABLE #Table2
DROP TABLE #TableSol
Thanks,
PSB
March 8, 2016 at 7:58 am
SELECT [Text],
IsContains = CASE WHEN EXISTS (
SELECT *
FROM #Table2
WHERE Deliv LIKE '%' + [Text] + '%'
) THEN 'Yes' ELSE 'No' END
FROM #TableSol
-- Gianluca Sartori
March 8, 2016 at 8:06 am
Need the unmatched as well .
SELECT 'ABCvvv' AS Text ,'No'
March 8, 2016 at 8:17 am
You sample data doesn't include it, you're missing an UNION ALL before it.
-- Gianluca Sartori
March 8, 2016 at 8:33 am
Thanks!
March 8, 2016 at 9:16 am
When it comes to performance , it's very poor. Can we improve performance for this query.
March 8, 2016 at 9:49 am
It has to scan the whole table to evaluate the LIKE predicate, so no, it can't be very fast.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply