CODEWITHSUNDEEP
sqlsql-serverselect
Bishan
Bishan

Reputation: 15702

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.

Upvotes: 0

Views: 126

Answers (4)

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

This works

SELECT * FROM TAG_DATA
      where TAGS like '%[BUS,CAR],[CAR,BUS]%'

Upvotes: 0

TechDo
TechDo

Reputation: 18629

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+'%'

Upvotes: 2

Pooh
Pooh

Reputation: 107

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

Upvotes: 2

Anup Agrawal
Anup Agrawal

Reputation: 6669

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,%'

Upvotes: 0

Related Questions