CODEWITHSUNDEEP
asp.netsql
happysmile
happysmile

Reputation: 7777

issue in sql Query

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?

Upvotes: 0

Views: 204

Answers (7)

Joel Coehoorn
Joel Coehoorn

Reputation: 415755

Delimited values in columns is almost always a bad table design. Fix your table structure.

If for some reason you are unable to do that, the best you can hope for is this:

SELECT * FROM [MyTable] WHERE items LIKE '%CRICKET%'

This is still very bad, for two important reasons:

  1. Correctness. It would return values that only contain the word cricket. Using your tennis example, what if you also had a "tennis shoes" item?
  2. Performance. It's not sargable, which means the query won't work with any indexes you may have on that column. That means your query will probably be incredibly slow.

If you need help fixing this structure, the solution is to add another table — we'll call it TableItems — with a column for your ItemID that will be a foreign key to your original table and an item field (singular) for each of your item values. Then you can join to that table and match a column value exactly. If these items work more like categories, where you want to rows with the "Cricket" item to match the same cricket item, you also want a third table to be an intersection between your original table and the other one I just had you create.

Upvotes: 2

Raj More
Raj More

Reputation: 48016

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

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)

Upvotes: 0

Peter
Peter

Reputation: 48958

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 3

Sani Huttunen
Sani Huttunen

Reputation: 24385

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

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

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.

Upvotes: 5

Related Questions