SOQL query help

[domdick]

Hello,

I have 1000 list of product names like

Name:

8 online

12 lives

34 services

Additional

Pages

Landing

Build

Now i need to build a list which can be filtered based on the straight alphabet.

A | B | C | D | .....| Other |

so query will be

Query: [ Select Name from Catelog__c where Name Like :string ]
//:string will get the param by selection like A% or B%  

 But i am strugling to make solution if the current page get param value == 'Other' then Query should display non-alphabetic values.

Any solution?

Thanks

[ibtesam]

Hi Dom,

You can get the products and store them in a list.

And on click of other just search for products which starts other than alphabets

Use Pattern p = Pattern.compile('^[a-zA-Z]*$');

Matcher m = p.matcher(productname);

if(!m.matches())  is true then return that product.

Try this approach

[Valnavjo]

Hello Domdick,

There are several options here, one of them is dynamic SOQL:

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_soql.htm

Hope this helps,

JVN

[domdick]

@ibtesam, Thanks for the quick respond!

How can we pass this matcher to SOQL query? detail exmaple would be much appriciated.

Thanks,

[ibtesam]

The solution i gave should not be passed to SOQL query.
Just collect the Products in a method and iterate over it using for loop and check the pattern of each product name.

if you want to avoid pattern technique, then follow this.

create a set of alphabets

Set<String> alphaSet = new Set<String>{'a','b','c'...till z};

List<product> nonalphaList = new List<Product>();

For( Product p : [select id,name from product ] ){

if(!alphaSet.contains( p.name.substring(0,1).toLower() ) )

nonalphaList.add(p);

}

In above code i am checking if first character of each product is alphabet or not, if not i am adding to a list

at the end of code i have my non alphabetic list ready.

[krisk]

Here is a good little trick

create a custom formula field on Product object and call it product_name_starts_with_no_alpha and make it return type of Text - "true" or "false"

Copy this in formula and replace FirstName with your Field Name 

Then in SOQL query use WHERE product_name_starts_with_no_alpha="true" 

IF(OR(BEGINS(FirstName,"A"),

BEGINS(FirstName,"B"),
BEGINS(FirstName,"C"),
BEGINS(FirstName,"D"),
BEGINS(FirstName,"E"),
BEGINS(FirstName,"F"),
BEGINS(FirstName,"G"),
BEGINS(FirstName,"H"),
BEGINS(FirstName,"I"),
BEGINS(FirstName,"J"),
BEGINS(FirstName,"K"),
BEGINS(FirstName,"L"),
BEGINS(FirstName,"M"),
BEGINS(FirstName,"N"),
BEGINS(FirstName,"O"),
BEGINS(FirstName,"P"),
BEGINS(FirstName,"Q"),
BEGINS(FirstName,"R"),
BEGINS(FirstName,"S"),
BEGINS(FirstName,"T"),
BEGINS(FirstName,"U"),
BEGINS(FirstName,"V"),
BEGINS(FirstName,"W"),
BEGINS(FirstName,"X"),
BEGINS(FirstName,"Y"),
BEGINS(FirstName,"Z"))
,"false","true")