+ Start a Discussion
khanWebgurukhanWebguru 

Please help me in LIKE query

I have Product object in salesforce and there are some products like 'MQX', 'ARC 600' etc

 

I am trying to get records through LIKE query like:

 

results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like \'%'+filterby+'%\'  order by ' + sortFullExp);

 

When I set filterby variable to "MQX" its not returning records :smileysad:... When I tried tro get records for "ARC"also givinig me NULL but when I ask for "600" then its returning fine also when I requested " MQX"  mean with one space as prefix also getting result but not getting exact match but getting like "Prodcut of MQX" etc......please have a look on following code

 

private void BindData(Integer newPageIndex)
    {
        try
        {
                string sortFullExp = sortExpression  + ' ' + sortDirection;
                searchText=sortFullExp;
                if(filterby == null || filterby == '')
                {                    
                      //searchText='Coming';
                }
                else if(filterby != '')    
                {
                       
                        results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like \'%'+filterby+'%\'  order by ' + sortFullExp);
                      
                      
                    
                }                         
    
            pageResults = new List<Product2>();
            Transient Integer counter = 0;
            Transient Integer min = 0;
            Transient Integer max = 0;
            if (newPageIndex > pageNumber)
            {
                min = pageNumber * pageSize;
                max = newPageIndex * pageSize;
            }
            else
            {
                max = newPageIndex * pageSize;
                min = max - pageSize;
                //min = (min <>
            }
            for(Product2 a : results)
            {    
                counter++;
                if (counter > min && counter <= max)
                    pageResults.add(a);
            }
            pageNumber = newPageIndex;
            if (pageResults == null || pageResults.size() <= 0)
            {
                ApexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO,'Data not available for this view.'));
               results=null;
               pageResults=null;
            }
        }
        catch(Exception ex)
        {
            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.FATAL,ex.getMessage()));
        }
    }

 

I have 7000 products in my object. Please help me in this. I want to get actual records for LIKE query.. Please reply ASAP Thanks in advance.

bob_buzzardbob_buzzard

You will need to add the wildcard characters to the parameter before binding it to the SOQL as a variable.

 

E.g.

 

String wildcardFilterBy='%'+filterby+'%';

 

results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like :wildcardFilterBy order by ' + sortFullExp);

 

khanWebgurukhanWebguru
:( Still same problem,,,,, working not in some cases like MQX although I had actual in my database :(
bob_buzzardbob_buzzard

Have you tried running an instance of your query through the schema explorer of the IDE? 

 

khanWebgurukhanWebguru
Icouldn't get ur point how can in development tool bar?
bob_buzzardbob_buzzard

If you are running the Force.com IDE (Eclipse based development tool), you can click on the salesforce.schema link in the project explorer and hand-craft SOQL queries to see if you get the matches you expect.  I think you can do the same thing from Apex DataLoader.

 

 

TehNrdTehNrd

This works for me. I have found that putting the wildcards directly into your query string doesn't always work as expected.

 

 

String entry = '%' + searchValue + '%';
String queryString = 'Select Id, Name from Account where Name like :entry order by Name limit 1000';

 

 

 

Message Edited by TehNrd on 08-13-2009 08:21 AM
khanWebgurukhanWebguru
:( Same Results