6

I have two tables A and B. Table A has columns ID, Name and Value. Among other columns in table B, there is a column called IssueID. A.Value has values something like 'ForSymbol12345' and B.IssueID has values like '12345'. I am able to join these two tables on some ID columns in respective tables. However, I only want to select those rows where B.IssueID is present in A.Value value. In other words, B.IssueID is a substring of A.Value.

Can it be done in SQL? I tried using CONTAINS(string, 'value to search for') but apparently second parameter must be string and cannot be column name. I tried like 

CONTAINS(A.Value, B.IssueID)

But it gives an error saying 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?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
LearningAsIGo
  • 1,240
  • 2
  • 11
  • 23
  • For what it's worth, the CONTAINS keyword does not support a dynamic search parameter. You must use a hard-coded string. See the documentation: https://msdn.microsoft.com/en-us/library/ms187787.aspx – JDB Mar 03 '16 at 17:00

2 Answers2

6

Use the LIKE operator with a JOIN.

SELECT A.*, B.*
FROM A
INNER JOIN B
ON A.Value LIKE CONCAT('%', B.IssueID, '%')
Glen Selle
  • 3,966
  • 4
  • 37
  • 59
Chendur
  • 1,099
  • 1
  • 11
  • 23
  • I should have mentioned that I am using SSMS 2008 and CONCAT fn was introduced in SSMS 2012. However, I did find an answer here http://stackoverflow.com/a/21702750/3482656 – LearningAsIGo Feb 29 '16 at 18:20
  • 2
    The second edit is right (i.e. your original answer was correct) - However, the OP says the tables join on keys in the respective tables as well on the substring match, so your `INNER JOIN` should probably be on these (unnamed) keys with the your `LIKE` in the join with AND or in a `WHERE` clause. – Joel Brown Feb 29 '16 at 18:21
  • You said "I only want to select those rows where B.IssueID is present in A.Value value". So B.IssueID is substring of A.Value. So it should be: ON B.IssueID LIKE CONCAT('%', A.Value, '%') – André Bonna Feb 29 '16 at 18:21
  • @JoelBrown you are correct. I joined table on keys in respective tables and I included this like condition in where clause. – LearningAsIGo Feb 29 '16 at 18:23
  • This is functional only if B is simple, discrete words, not a full text search expression. LIKE is not a straight replacement for CONTAINS. I just wish there was a more straightforward way to approximate CONTAINS with sys.dm_fts_parser. It would be great if you could parse your expressions and store the query tree. – user1664043 May 08 '20 at 16:54
2

CONCAT option mentioned below by evil333 could have worked but I am using SSMS 2008 and CONCAT was introduced in SSMS 2012. So, I found a work around on that here

https://stackoverflow.com/a/21702750/3482656

You can do something like 

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

I hope this helps.

Community
  • 1
  • 1
LearningAsIGo
  • 1,240
  • 2
  • 11
  • 23
  • Isn't SSMS 'Sql Server Management Studio'? What does it have to do with SQL Server features? – Leonardo Herrera Mar 01 '16 at 21:10
  • 1
    @LeonardoHerrera - Microsoft releases a new version of SSMS with each version of SQL Server. The flurry of version numbers can be pretty confusing to newbies, but it's safe to say that if the OP is using SSMS 2008, then the newest version of SQL Server they could be working with is SQL Server 2008. – JDB May 24 '16 at 21:07