当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL search column where one item in column is substring of another item

问题描述:

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.

ASP

ASP.NET

PHP

HTML

J2EE

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?

UPDATE:

Can the solution also account for the case where the column string contains both ASP and ASP.NET?

UPDATE 2:

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 item.

分享给朋友:
您可能感兴趣的文章:
随机阅读: