Post reply

Stored Procedures for multiple like condition search

  • November 28, 2010 at 12:06 am

    #229680

    Hi All,

    Please go through the screenshot which i had attached

    I have a table with 4 columns.The first column is a list of teams.The other three columns specify whether the team member with the particular id is present on any particular day .Say for example in the third row the running Team has 2 and 3 which means team member with the id 2 and 3 are present.Multiple values are seperated by the pipe symbol '|'.

    I have a Query like this

    Select Teamname from tblTeam where Running not like '%|2|%' and Running not like '%|3|%' and Running not like '%|7|%' and Biking not like '%|1|%' and Biking not like '%|4|%' and Biking not like '%|7|%' and shooting not like '%|3|%' and shooting not like '%|4|%' and shooting not like '%|7|%'

    Basically i am searching the columns with like clause.The search criteria for each column may vary.In the above query i am searching for Teams where Running column does not contain 2,3 and 7,Biking column does not contain 1,4 and 7 and the shooting column does not contain 3,4 and 7.

    I would like to set a stored procedure where i would be able to pass 3 set of parameters from the code behind(I am using VB.net) for Running column search,Biking column search and the shooting column search.

    Can someone help me out for writing a simple stored procedure.

    Regards

    Ashvin

  • November 28, 2010 at 1:52 am

    #1256005

    The easiest way would be using a normalized table structure: Teams(TeamID,TeamName),Avtivities(ActivityID,ActivityName), and a table to join both: TeamActivityDays(TeamID,ActivityID,Day) (There are most probably people out there having the opinion to need another table in between to match only Teams and Activities with another separate ID and to use that one in the last table but I wouldn't go that far in this case).

    To change your structure you'd nee to look into UNPIVOT as well as a split string function like DelimitedSplit8K (search this site for the code).

    Based on such a normalized table structure your query will be much easier...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply