+ Start a Discussion
plapla 

SOQL does not contain statement

Hello,

 

How can I write a SOQL statement with a clause "does not contain" ? I've tried this Carrier_ID__c not like '%TEST%' but it does not work. I want to use this SOQL for the dataloader to export the data. Please advise.

 

Thanks

Paul

 

Best Answer chosen by Admin (Salesforce Developers) 
SRajesh8SRajesh8

Hi,

 

I created one formula field salesforceID__c  in account object and added the below code

 

CASESAFEID(Id)

 

in formula and when ever u create a account record this filed salesforceID store 18 digits salesforce record id.By this u can retrieve 18 digits ID in reports.

 

Regards,

Rajesh.

All Answers

Avidev9Avidev9

The correct syntax is

 

NOT (Carrier_ID__c  like '%TEST%' )

 

plapla

 

I just tried NOT (Carrier_ID__c  like '%TEST%' ) and it's still not working. I still got the error.

 

Please help.

 

thanks

 

 

Avidev9Avidev9

I just tried 

 

List<Account> acc = [SELECT Id FROM Account WHERE not (Name like '%test')];

 And it worked as expected

plapla

 

I've tried it again and it does not work for me. I think it works a little different for dataloader tool. This sql is for dataloader tool to export data. Any idea will make it work?

 

Thanks,

Paul

 

 

 

Puja_mfsiPuja_mfsi

HI,

 

SRajesh8SRajesh8

Hi,

 

As above mentioned is correct, but u still u not getting means then try to generate report and in this we have "does not contains" filter and retrieve the required result.

 

Regards,

Rajesh.

plapla

The report generates only 15 digit ID and that's the reason why I use the dataloader to generate the report as it will give me the 18 digit ID.

 

Thanks

 

SRajesh8SRajesh8

Hi,

 

Create one custom formula for object which u want 18 digits in report and paste the below code in it and save.

 

CASESAFEID(Id) It will  store the 18 digits value and that value can retrieve in reports.
 
Regards,
Rajesh.
plapla

Can you show me an example of how you do it? let's use Account object for the report that I want for 18 digit ID.

 

Thanks

Paul

 

SRajesh8SRajesh8

Hi,

 

I created one formula field salesforceID__c  in account object and added the below code

 

CASESAFEID(Id)

 

in formula and when ever u create a account record this filed salesforceID store 18 digits salesforce record id.By this u can retrieve 18 digits ID in reports.

 

Regards,

Rajesh.

This was selected as the best answer
plapla

Thanks. An 18 digit account ID is what I need for the report.

 

Paul

 

SRajesh8SRajesh8

Hi,

 

In report u can use that custom field SalesforceID which we will retrive 18 digits of ur record Id.

Please try once again.

 

Regards,

Rajesh.

plapla

Thanks Rajesh. It works for me now. That's what I meant in my previous message.

 

Paul

 

Mina Michel GorgyMina Michel Gorgy
Hello pla, I think you'll just need to enclose this part inside brackets so that it works... I tried it now and these brackets made it work!
Beto Carvalho 11Beto Carvalho 11
The operator NOT has to be inside the parenthesis (NOT Carrier_ID__c  like '%TEST%' ). Just tried this syntax and it works.

"NOT (Carrier_ID__c  like '%TEST%' )" does not work.
Dia MehdawiDia Mehdawi
BEST ANSWER


Beto Carvalho 11
The operator NOT has to be inside the parenthesis (NOT Carrier_ID__c  like '%TEST%' ). Just tried this syntax and it works.

"NOT (Carrier_ID__c  like '%TEST%' )" does not work.