CODEWITHSUNDEEP
sqlsql-servert-sql
jorge_vicente
jorge_vicente

Reputation: 368

Find text of a column in other column with T-SQL

I have a doubt to find text from one column in a table; in other column from other table.

Imagine that you have this columns:

enter image description here

And you want to find the COMPLETE text of [A].X in [B].Y

And to discover where do you have the match. The colour yellow show this choice: enter image description here

I have been thinking to use the "CONTAINS" function, but I think that it could be not the best idea. Because you have to write the text that you need to find (instead of a complete text of a column).

CONTAINS T-SQL

I thought that it could be like this:

Use AdventureWorks2012;  
GO  
SELECT [B].Y  
FROM Production.Product  
WHERE CONTAINS(([A].Y), [A].X);  

But it doesn't work.

Which is the best option?

I am using SQL SERVER V17.0

Thanks!!!

Upvotes: 0

Views: 4344

Answers (4)

CuriousKid
CuriousKid

Reputation: 605

I agree with @GordonLinoff. Like is what i would also go for but i want to make one improvement in the answer provided. 

CREATE TABLE #TempA (ColumnX VARCHAR(10));
CREATE TABLE #TempB (ColumnY VARCHAR(100));

INSERT #TempA
VALUES ('fish')
    ,('burguer')
    ,('sugar')
    ,('tea')
    ,('coffee')
    ,('window')
    ,('door');

INSERT #TempB
VALUES ('I steam potatoes')
    , ('I like potatoes')
    ,('I eat sugar')
    ,('I eat sugar with onions')
    ,('I have a car coffee')
    ,('I don''t like dogs')
    ,('Window is clean')
    ,('Open the door')
    ;


SELECT b.ColumnY
    ,a.ColumnX
FROM #TempB b
INNER JOIN #TempA a ON ' '+ b.ColumnY + ' ' LIKE '% ' + a.ColumnX + ' %'

This will take care of the TEA to be not found in STEAM.

Upvotes: 1

Wendy
Wendy

Reputation: 660

CREATE TABLE #TempA
(ColumnX VARCHAR(10)
);
CREATE TABLE #TempB
(ColumnY VARCHAR(100)
);
INSERT #TempA
VALUES('fish'),('burguer'),('sugar'),('tea'),('coffee'),('window'),('door');

INSERT #TempB
VALUES('I like potatoes'),('I eat sugar'),('I eat sugar with onions'), ('I have a car'),('I don''t like dogs');

SELECT *
FROM #TempB b
WHERE EXISTS(SELECT 1 FROM #TempA a WHERE CHARINDEX(a.ColumnX, b.ColumnY,1) > 0);

Upvotes: 1

EMUEVIL
EMUEVIL

Reputation: 512

Here is a quick example searching a list of strings for a particular set of words defined in another table. This example just searches through the system error messages text and looks for the words 'overflow' and 'CryptoAPI', but you'll substitute the words table with your 'A' and the 'sys.messages' table with your table 'B'

NOTE: this isn't the most efficient way to search large amounts of text.

-- CREATE TEMP TABLE WITH WORDS TO MATCH
CREATE TABLE #words (
    [Word] nvarchar(100)
)

-- SAMPLE STRINGS
INSERT INTO #words VALUES ('overflow')
INSERT INTO #words VALUES ('CryptoAPI')

-- SEARCH THROUGH SYSTEM ERROR MESSAGES FOR SAMPLE STRINGS
SELECT [W].[Word] AS 'Matched word'
    , [M].[text]
FROM [sys].[messages] AS [M]
    JOIN #words AS [W]
        ON [M].[text] LIKE '%' + [W].[Word] + '%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would go for like:

select b.y, a.x 
from b join
     a
     on b.y like '%' + a.x + '%' ;

There is not, however, a really efficient way to do this logic in SQL Server.

Upvotes: 4

Related Questions