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

sql - Query a table to find records with given text

问题描述:

I have below 3 records in my table(TAG_DATA) column(TAGS).

car,bus,van

bus,car,ship,van

ship

I wrote a query to get records which has car and bus as below.

SELECT * FROM TAG_DATA

where TAGS like '%car, bus%'

But above query return only below record.

car,bus,van

But i need to get output as below. because both records have car and bus

car,bus,van

bus,car,ship,van

How can i write a query for this ? I'm using MS SQL Server.

UPDATED

I'm selecting tags from multi select combobox in my application. so i need to give text in that. so can't use and/ or in my query.

网友答案:

Please try:

DECLARE @input NVARCHAR(MAX)='car, bus'

SELECT DISTINCT B.*
FROM(
    SELECT 
         LTRIM(Split.a.value('.', 'VARCHAR(100)')) AS CVS  
    FROM  
    (
         SELECT
            CAST ('<M>' + REPLACE(@input, ',', '</M><M>') + '</M>' AS XML) AS CVS  
    ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a) 
)x  INNER JOIN TAG_DATA b on TAGS like '%'+CVS+'%'
网友答案:

Here, only records having car, bus tags in sequence will be fetched.

For the desired result, below query is beneficial :

SELECT * FROM TAG_DATA
where TAGS like '%car, bus%' or  TAGS like '%bus, car%'

SQL FIDDLE

网友答案:
SELECT * FROM TAG_DATA
  where TAGS like '%car,%' and TAGS like  '%bus,%'

I intentionally used the commas. It depends on your data The following query will also work atleast for the example above.

SELECT * FROM TAG_DATA
  where TAGS like '%car,%' and TAGS like  '%bus,%'
网友答案:

This works

SELECT * FROM TAG_DATA
      where TAGS like '%[BUS,CAR],[CAR,BUS]%'
分享给朋友:
您可能感兴趣的文章:
随机阅读: