issue in sql Query

Question

I have an column in table where this column name is items it contains value like this

itemID  items
1        school,college
2        place, country
3        college,cricket
4        School,us,college
5        cricket,country,place
6        football,tennis,place
7        names,tennis,cricket
8        sports,tennis

Now I need to write a search query

Ex: if the user types 'cricket' into a textbox and clicks the button I need to check in the column items for cricket.

In the table I have 3 rows with cricket in the items column (ItemId = 3, 5, 7)

If the user types in tennis,cricket then I need to get the records that match either one. So I need to get 5 row (ItemId = 3, 5, 6, 7, 8)

How do I write a query for this requirement?

Answer 1

I think that in the interest of validity of data, it should be normalized so that you split the Items into a separate table with an item on each row.

In either case, here is a working sample that uses a user defined function to split the incoming string into a Table Variable and then uses JOIN with a LIKE

CREATE FUNCTION dbo.udf_ItemParse
(
    @Input VARCHAR(8000), 
    @Delimeter char(1)='|'
)
RETURNS @ItemList TABLE 
(
    Item VARCHAR(50) ,
    Pos int
)
AS
BEGIN

DECLARE @Item varchar(50)
DECLARE @StartPos int, @Length int
DECLARE @Pos int

SET @Pos = 0

WHILE LEN(@Input) > 0
BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @Input)
    IF @StartPos < 0 SET @StartPos = 0
     SET @Length = LEN(@Input) - @StartPos - 1

    IF @Length < 0 SET @Length = 0
     IF @StartPos > 0
     BEGIN
      SET @Pos = @Pos + 1
      SET @Item = SUBSTRING(@Input, 1, @StartPos - 1)
      SET @Input = SUBSTRING(@Input, @StartPos + 1, LEN(@Input) - @StartPos)
     END
     ELSE
     BEGIN
      SET @Pos = @Pos+1
      SET @Item = @Input
      SET @Input = ''
     END

     INSERT @ItemList (Item, Pos) VALUES(@Item, @Pos)
    END
    RETURN
END 
GO
DECLARE @Itemstable TABLE
(
    ItemId INT,
    Items VarChar (1000)
)
INSERT INTO @Itemstable 
SELECT 1 itemID, 'school,college' items UNION
SELECT 2, 'place, country' UNION
SELECT 3, 'college,cricket' UNION
SELECT 4, 'School,us,college' UNION
SELECT 5, 'cricket,country,place' UNION
SELECT 6, 'footbal,tenis,place' UNION
SELECT 7, 'names,tenis,cricket' UNION
SELECT 8, 'sports,tenis'

DECLARE @SearchParameter VarChar (100) 
SET @SearchParameter = 'cricket'

SELECT DISTINCT ItemsTable.*
FROM @Itemstable ItemsTable
    INNER JOIN udf_ItemParse (@SearchParameter, ',') udf
     ON ItemsTable.Items LIKE '%' + udf.Item + '%'


SET @SearchParameter = 'cricket,tenis'

SELECT DISTINCT ItemsTable.*
FROM @Itemstable ItemsTable
    INNER JOIN udf_ItemParse (@SearchParameter, ',') udf
     ON ItemsTable.Items LIKE '%' + udf.Item + '%'

Answer 2

You need to start by redesigning your database as this is is a very bad structure. You NEVER store a comma delimited list in a field. First think about waht fields you need and then design a proper database.

Answer 3

For a single item:

SELECT itemID, items FROM MyTable WHERE items LIKE '%cricket%'

For multiple items:

SELECT itemID, items FROM MyTable WHERE items LIKE '%tennis%' or items LIKE '%cricket%'

You'll need to parse the input and split them up and add each item to the query:

items LIKE '%item1%' or items LIKE '%item2%' or items LIKE '%item3%' ...

Answer 4

The very bad structure of this table (holding multiple values in one column) is the reason you are facing this issue. Your best option is to normalize the table.

But if you can't, then you can use the "Like" operator, with a wildcard

  Select * From Table
  Where items Like '%cricket%'

or

  Select * From Table
  Where items Like '%cricket%' 
    or items Like '%tenis%'

You will need to dynamically construct these sql queries from the inputs the user makes. The other alternative is to write code on the server to turn the comma delimited list of parameters into a table variable or temp table and then join to it..

Answer 5

Why exactly are you using a database in the first place?

I mean : you are clearly not using it's potential. If you like using comma separated stuff, try a file.

Answer 6

In MySQL, create a fulltext index on your table:

CREATE FULLTEXT INDEX fx_mytable_items ON mytable (items)

and issue this query:

SELECT  *
FROM    mytable
WHERE   MATCH(items) AGAINST ('cricket tennis' IN BOOLEAN MODE)