function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
sfdc integrator.ax1790sfdc integrator.ax1790 

Regular Expression in SOQL

Hi,

 

I need to query records[soql] which starts with non-alphabet characters.

 

Please help me.

 

Suresh S

Best Answer chosen by Admin (Salesforce Developers) 
GlynAGlynA

That's a clever idea.  I did not consider modifying the object to make the task easier.  Good job!  -Glyn

All Answers

RockzRockz

Hi..

 

Hope this will help :

 

http://www.salesforcegeneral.com/salesforce-articles/regular-expressions-in-apex-code.html

 

 

Please accept my answer as a solution if my solution was helpful. This will make it available to others as a proper answer. If you felt that I went above and beyond please give me Kudos by clicking on on the star icon.

 

Thanks,

Cool Sfdc

GlynAGlynA

Suresh,

 

Unfortunately, SOQL does not support regular expressions.  But you can do a long WHERE clause:

 

SELECT Id, Name FROM sObject

WHERE Name NOT LIKE 'A%' AND Name NOT LIKE 'B%' AND Name NOT LIKE 'C%' AND ... AND Name NOT LIKE 'Z%'

 

Since SOQL compares in a case-insensitive manner, you only need 26 'NOT LIKE' comparisons, not 52.

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

GlynAGlynA

Suresh,

 

Try something like this:

 

    List<String> alphabet = new List<String>
    {   'a','b','c','d','e','f','g','h','i','j','k','l','m',
        'n','o','p','q','r','s','t','u','v','w','x','y','z'
    };

    List<String> comparisons = new List<String>();
    for ( String letter : alphabet )
    {
        comparisons.add( 'Name NOT LIKE \'' + letter + '%\'' );
    }

    String whereClause = String.join( comparisons, ' AND ' );

    List<sObject> queryResult = Database.query( 'SELECT Id, Name FROM sObject WHERE ' + whereClause );

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

sfdc integrator.ax1790sfdc integrator.ax1790

GlynAGlynA

Well, I've done more research and even this approach won't work because you can't do "NOT LIKE", you can only do "LIKE".

 

If you know what non-alphabetic characters the record names will start with, you can set up a big where clause (modify my code to do so):

 

WHERE Name LIKE '1%' OR Name LIKE '2%' OR...

 

I've tested this with a large number of terms in the expression (roughly 60 or so) and had no problem.  It's ugly, but it works.

 

-Glyn

sfdc integrator.ax1790sfdc integrator.ax1790

Thanks 

GlynAGlynA

That's a clever idea.  I did not consider modifying the object to make the task easier.  Good job!  -Glyn

This was selected as the best answer