Is there a way to for an sql statement to search if a column string with multiple items contains a certain item, but not include a certain item that is a substring. The following is the current sql statement that I am using.
select * from tbltest where platform like '%item%'
platform is the column string that could have multiple items in the string. Item is the specific item that I am searching for in the platform string.
The following is an example of what I am describing and the items that I am searching for. Items to search for in string (These are in a dropdownlist for the user to select). Notice that ASP would be considered a substring of ASP.NET and if the user selects to search for ASP in the column string of items, the records returned would also include the ASP.NET items based on the sql statement that I write above.
So is there a way to add a statement in the where portion of an SQL statement that would do what I am describing above or specifically, based on the example above, search for the ASP items without returning the ASP.NET items?
Can the solution also account for the case where the column string contains both ASP and ASP.NET?
This is a better description of what I'm looking for. Thanks.
SQL search column where one item in column is substring of another item Update
If you want to require that an item be surrounded by spaces you can add them on either side of your list as well as your term:
select * from tbltest where ' '+platform+' ' like '% item %'
Ideally data is not stored in lists, as this searching will not be terribly efficient.
select * from tbltest where (platform like 'item%' or platform like '% item%') and (platform like '%item' or platform like '%item %')
What this does is check whether the
item is surrounded by spaces, the beginning and/or the ending of the string.
A requirement would be that there's no
item with a space and you always split on a space.
Otherwise you'd need another char to split on.
I would do something like this:
select * from tbltest where platform like '%item%' and platform not like '%item.%' -- searching for ASP and platform not like '%.item%' -- searching for NET
Notice the additional dot before and after