locked
How to use filteration for multiple ID if they are in comma seperated (,) using SQL/ASP.NET/C# RRS feed

  • Question

  • User-325945362 posted

    hello friends,

    this is my code to append SQl Query Where clause,

    if (strCallerID != "" && strCallerID!= null)
           {          
               sb.Append("AND QH.RequestID IN ('" + strCallerID + "')  ");
           }

    my question is if string values are multiple and in comma seperated(,) EG:001,002,003,004,005

    so how should i code to get the exact match do i need to use array or sumthing else can sum one give the code for this

    Thanxs in advance

    Thursday, January 5, 2012 5:07 AM

Answers

  • User-325945362 posted

    OK. I fixed this Issue.

    Here it goes......................

    if (strCallerName != "" && strCallerName != null)
            {            
                string ABC = "";

                string[] strCallerNameSplit = strCallerName.Split(',');
                for (int i = 0; i < strCallerNameSplit.Length; i++)
                {
                    string strCallerNameMultiple = strCallerNameSplit[i];
                    if (ABC == "")
                    {
                        ABC = " QH.Name Like '%" + strCallerNameMultiple + "%' ";
                    }
                    else
                    {
                        ABC = ABC + " OR QH.Name Like '%" + strCallerNameMultiple + "%' ";
                    }
                }
                if (ABC != "")
                {
                    sb.Append("AND (" + ABC + ") ");
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 16, 2012 8:31 AM

All replies

  • User551462331 posted

    please elaborate... what is the issue? even if u have comma separated values in strCallerID... it will be fine as u have used RquestID in ().. clause

    this is valid sql query

    select * from tablename where columnname in (1,2,3)

    hope this helps...

    Thursday, January 5, 2012 5:40 AM
  • User-325945362 posted

    My Queri is as follows:

    I have a form where user can select multiple option to get the Data of the Client

    EG:user can select Client ID,Name,Designation,Email Add, Phone no. , etc etc ....

    iam firing an select query which combines 4tables.

    and in the where caluse iam appending the filters used by the USER. Eg: ID, Name,Add,Phone no., etc etc....

    now,

    when user wanted to know information of some client and he knows just their name like Manoj,Kumar,Rashid,Hanif

    he will write all this names in the textbox of Clinet Name... so how should i give the where clause

    I tried in this way bt iam nt getting to it.

    SELECT * FROM dbo.Member WHERE Name LIKE IN ('%Manoj,KUMAR,Rashid,Hanif%')

    any any one give me the code how should i give the wherer clause here.....

    Thanks a lot in advance..

    Thursday, January 5, 2012 7:31 AM
  • User551462331 posted

    this should simply be

    SELECT * FROM dbo.Member WHERE Name LIKE '%Manoj,KUMAR,Rashid,Hanif%'

    hope this helps...

    Thursday, January 5, 2012 9:29 AM
  • User-325945362 posted

    Thanks for the rep.

    but this is not working it is returning an blank row. LIKE is not working for multiple name if i am specifying single name then works like charm...

    but in the case of multiple name comma seperated(,) it is not working neither giving error...!!!

    In my case user can specify multiple name in the textbox with comma seperated(,).

    Can any one help me in this

    Thanks..

    Thursday, January 5, 2012 11:51 PM
  • User551462331 posted

    in case of like, it is required that, all the contents inside %% match exactly with data...

    ex. suppose user enters value like Manoj,KUMAR,Rashid,Hanif

    and in database column "name", data is stored as Manoj, KUMAR, Rashid, Hanif

    note extra sapce afte every name.... in this case, query would not work.... it should match exactly including spaces etc...

    also, please confirm that u have data stored in database column as comma separated named.... and not in separate records...

    hope this helps...

    Friday, January 6, 2012 5:15 AM
  • User-2139489267 posted

    SELECT * FROM dbo.Member WHERE Name LIKE IN ('%Manoj,KUMAR,Rashid,Hanif%')

    This won't work in case of Name. You should have diff criteria handled in your stored procedure.

    For Name to make it work, check out this link.

    Friday, January 6, 2012 5:33 AM
  • User-325945362 posted

    Thanks for reply ::

    the example shown in the above link is as follows...

    DECLARE @WordsToSearch VARCHAR(1000)
    DECLARE @Query VARCHAR(2000)
    SET @WordsToSearch = 'Mathematics brain'
     
    SET @Query = 'SELECT * FROM [Books] WHERE [Description] LIKE ''%' + REPLACE(@WordsToSearch, ' ', '%'' OR [Description] LIKE ''%') + '%'''
     
    PRINT @Query
    EXEC(@Query)
    i:e
    select * from [Books] where [Description] LIKE '%Mathematics%' OR [Description] LIKE '%brains%'
    bt i cant hard code it user can specify many name of clients of which he wants the details , so how could i code to make it dynamically....
    Thanks a lot in advance..
    Friday, January 6, 2012 6:57 AM
  • User-2139489267 posted

    As you said, user can write names delimited by comma, so you could have something like below :

    DECLARE @WordsToSearch VARCHAR(1000)
    DECLARE @Query VARCHAR(2000)
    SET @WordsToSearch = 'Manoj,KUMAR,Rashid,Hanif'
     
    SET @Query = 'SELECT * FROM [Member] WHERE [FirstName] LIKE ''%' + REPLACE(@WordsToSearch, ',', '%'' OR [FirstName] LIKE ''%') + '%'''
    print(@query)
    exec(@query)

    An alternate way would be to split your comma delimited string with Split function and use inner join to search from table.

    Edit: An alternate approach would be to do it at front end. Please check out this link

    Friday, January 6, 2012 7:34 AM
  • User-325945362 posted

    OK. I fixed this Issue.

    Here it goes......................

    if (strCallerName != "" && strCallerName != null)
            {            
                string ABC = "";

                string[] strCallerNameSplit = strCallerName.Split(',');
                for (int i = 0; i < strCallerNameSplit.Length; i++)
                {
                    string strCallerNameMultiple = strCallerNameSplit[i];
                    if (ABC == "")
                    {
                        ABC = " QH.Name Like '%" + strCallerNameMultiple + "%' ";
                    }
                    else
                    {
                        ABC = ABC + " OR QH.Name Like '%" + strCallerNameMultiple + "%' ";
                    }
                }
                if (ABC != "")
                {
                    sb.Append("AND (" + ABC + ") ");
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 16, 2012 8:31 AM
  • User-1070902567 posted

    string s1 = Request.QueryString["batchid"];
    string s2 = Request.QueryString["clientid"];
    I want to retrive data from table using the following query
    How can i write the query???
    SqlCommand cmd = new SqlCommand("select coupounurl from redirecturl where ClientId="+s " BatchId=" +s1, con);
    I am getting an error plz help me

    Thursday, November 1, 2012 4:19 AM