0

I am trying to modify following SOQL statement so that query only retrieves if first 3 characters of FirstName AND first 3 characters of LastName match.

This is part of my original SOQL statement.

SELECT id__c, 
       LName__c, 
       FName__c,
       FROM Patient__c
       where
       LName__c =:lname and FName__c =:fname

I know I should use an expression like LEFT(LName__c, 3), but I am not sure how I could apply.

Bottom is part of Apex code where this SOQL runs:

public PageReference searchPatients(){

    RowList = new List<TableRow2>();
    TableRow2 tr;

    if (fname != null) fname = fname.left(3);
    if (lname != null) lname = lname.left(3);

    for(Patient__c con : [SELECT sfprn__c, 
                                mrn__c,
                                LName__c, 
                                FName__c,
                                (select SurveySelection__c.SurveySelection__c from SurveySelections__r) 
                                FROM Patient__c
                                where ( mrn__c =:mrn ) 
                      or ( LName__c LIKE :lname + '%' and FName__c LIKE :fname + '%' ) LIMIT 1                      
                         ]){                                    
        tr = new TableRow2();
        tr.sfprn = con.sfprn__c;
        tr.mrn = con.mrn__c;
        tr.lname = con.LName__c;
        tr.fname = con.FName__c;
        tr.SurveySelection = con.SurveySelections__r.isEmpty() ? null : con.SurveySelections__r[0].SurveySelection__c;

        RowList.add(tr); 
     }
     return null;   
}

1 Answer 1

3

If you want to check if a field starts with a specific value, use LIKE with a terminating wildcard:

WHERE LName__c LIKE 'abc%'

Depending where you get these fname andlname` values from, you may need to add this wildcard yourself:

WHERE FName__c LIKE :fname + '%'
AND LName__c LIKE :lname + '%'

If you want to make sure you check exactly three characters, you can manipulate the bind variables as necessary, but have to ask yourself some questions. If the length is less than three, do you throw an exception? Anyway, if it's longer, you can, as mentioned above, trim it down to the desired length:

if (fname != null) fname = fname.left(3);
if (lname != null) lname = lname.left(3);

Here is a more complete syntax:

public List<Patient__c> matchFirstThreeCharacters(String firstName, String lastName)
{
    if (firstName != null) firstName = firstName.left(3);
    if (lastName != null) lastName = lastName.left(3);
    // what do you do if they are null? error case?

    return [
        SELECT FName__c, LName__c FROM Patient__c
        WHERE FName__c LIKE :firstName + '%'
        AND LName__c LIKE :lastName + '%'
    ];
}
8
  • If the requirement is about first three characters, how do I express it?
    – Java
    May 26, 2017 at 21:44
  • @KingJava You just make sure it starts with the bind value. If you want to, you can verify the length of the bind value and truncate it if necessary.
    – Adrian Larson
    May 26, 2017 at 21:45
  • I added other code to have a better picture. My question now is: On the "If" statement above, do you express inside SOQL statement? I guess I am not understanding how "bind value/variable" works.
    – Java
    May 26, 2017 at 22:44
  • @KingJava No, you would use the if blocks to truncate the name values if they are longer than you want, before binding them into your query.
    – Adrian Larson
    May 27, 2017 at 0:22
  • @KingJava Are you still stuck on this one then?
    – Adrian Larson
    May 29, 2017 at 15:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .