2

How can we write SOQL query which has where condition which will retrieve record whose field name ends with particular letters.

Example we have following opportunity names:

  • abcdEF
  • qwertyt
  • poiuyEF

From these I want to retrieve opportunities ending with 'EF' (abcdEF and poiuyEF) through SOQL queries

Note: 'EF%' can be used for names starting with 'EF"

6
  • 1
    have you tried %EF?
    – Ratan Paul
    May 5 '16 at 10:16
  • use like query. May 5 '16 at 10:16
  • I am using like query only. %EF is not working, it is giving all records where EF is in BETWEEN as well. I want those where it is at END only
    – Sarang
    May 5 '16 at 10:18
  • 1
    %EF means character ends with EF this should give correct output not sure why this is giving wrong output... Can you share your soql what you have tried?
    – Ratan Paul
    May 5 '16 at 10:20
  • I have tested it and the query is giving correct output.
    – Mr.Frodo
    May 5 '16 at 10:22
5

Use '%EF' with 'like'.

for eg: Select Id, name from account where name like '%a'

in your case:

Select Id, name from opportunity where name like '%EF'
2

Adding to Mr.Frodo's answer, this is taken from 'Getting Started with SOQL'.

Using the LIKE operator, we can retrieve records that match the substring provided.
The LIKE operator is mainly used to compare a value to all similar values using
wildcard characters. The LIKE operator is also called the pattern matching filtering
technique. Any record that matches the pattern alone will be retrieved, which
improves the filtering.

A sample query is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Name LIKE 'Test%'

The preceding query will retrieve all the Opportunity instances where the name
starts with Test.

The Opportunity instances whose names end with Test will not be included in
the results. If we also want to include names ending with Test, we have to use
another percentage symbol to the left so that it will be '%Test%'. When we do this,
any Opportunity instance with Name that includes Test will also be included in
the result. We have to use _ instead of % if we want to just match it with a single
character to its left or right. The LIKE operator is very useful if we are unsure about
the exact value with which we have to match. While using the LIKE operator, make
sure you have entered the correct matching pattern. For example, the '%Test%'
matching pattern will not be as efficient as 'Test%' due to the way indexes work
and may take a longer time to retrieve the result set of a large object.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.