Geek Answers Handbook

SQL - if a row in a column from one table contains a row in a column from a joined table

I have two tables A and B. Table A has column identifiers, name and value. Among the other columns in table B, there is a column named IssueID. A.Value has values ​​like "ForSymbol12345", and B.IssueID has values ​​like "12345". I can join these two tables on some identifier columns in the respective tables. However, I only want to select those rows where B.IssueID is present in the value of A.Value. In other words, B.IssueID is a substring of A.Value.

Can this be done in SQL? I tried to use CONTAINS (string, "value to search"), but apparently the second parameter should be a string and cannot be a column name. I tried both

CONTAINS(A.Value, B.IssueID)

But it gives an error saying that the second parameter is expected to be String, TEXT_LEX or Variable (a simplified example showing this below)

enter link description here

Can someone help me figure this out?

+6
source share
2 answers

Use the LIKE statement with JOIN .

 SELECT A.*, B.* FROM A INNER JOIN B ON A.Value LIKE CONCAT('%', B.IssueID, '%')
+5
source

The CONCAT option mentioned below evil333 might work, but I'm using SSMS 2008, and CONCAT was introduced in SSMS 2012. So I found work around here

fooobar.com/questions/69884 / ...

You can do something like

 A.value like '%' + cast(B.IssueID as varchar) + '%'

Hope this helps.

+2
source

More articles:


All Articles