CODEWITHSUNDEEP
sqlsql-servert-sqlfull-text-search
Jeyhun Rahimov
Jeyhun Rahimov

Reputation: 3787

SQL search query based on string - numbers separated with comma

I have a Product model that has a string property contains numbers separated comma. Also have a table related to this model.

public class Product 
{
  ....
  public string RelatedUserIdSet { get; set; }
}

//different variants
row1 - RelatedUserIdSet = "1,3,43,555,90,4";
row2 - RelatedUserIdSet = "3445,737,34,54";
row3 - RelatedUserIdSet = "3445,88,44,54,3";
row4 - RelatedUserIdSet = "";
row5 - RelatedUserIdSet = "44";
row6 - RelatedUserIdSet = "11,5,655,89,99,447,1455,12,112,658";
.....

I need a help to create a sql query to find products that it's RelatedUserIdSet contains given keyword. Assume that, if keyword will be keyword = 3, we'll get model1 and model3.

Upvotes: 0

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In relational databases, you should be storing this information as a junction table. But, that is not how it is stored in the source system. In SQL Server, you can solve this with like:

where ',' + RelatedUserIdSet + ',' like '%,' + cast(keyword as varchar(255)) + ',%'

Note the use of commas to delimit both the set contents and the query string. This prevents 3 from matching 3445.

Upvotes: 3

Related Questions