Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nidhi ShuklaNidhi Shukla 

Sql query that contains digit

I am trying to extract the data via data loader, I am looking for the following criteria.
First Name contains 0,1,2,3,4,5,6,7,8,9
How do I put this in the SQL query? 
Best Answer chosen by Nidhi Shukla
Prafull G.Prafull G.
Select Id, LastName, FirstName, City, Country, Email, IsActive, IsPortalEnabled FROM User WHERE IsActive = True AND IsPortalEnabled = True AND (FirstName LIKE '0%' OR Name LIKE '1%' OR FirstName LIKE '2%' OR Name LIKE '3%' OR FirstName LIKE '4%' OR Name LIKE '5%' OR FirstName LIKE '6%' OR Name LIKE '7%' OR FirstName LIKE '8%' OR Name LIKE '9%')

It looks ugly, but works fine.

All Answers

Prafull G.Prafull G.
Hi Nidhi,

Try using LIKE operator in SOQL query. Here is a snippet from documentations -
Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value. The LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards.
  • The % and _ wildcards are supported for the LIKE operator.
  • The % wildcard matches zero or more characters.
  • The _ wildcard matches exactly one character.
  • The text string in the specified value must be enclosed in single quotes.
  • The LIKE operator is supported for string fields only.
  • The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
  • The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
  • Don’t use the backslash character in a search except to escape a special character.
For example, the following query matches Appleton, Apple, and Appl, but not Bappl: 
SELECT AccountId, FirstName, lastname
FROM Contact
WHERE lastname LIKE 'appl%'

Thanks
Prafull
Nidhi ShuklaNidhi Shukla
@Prafull, What I am looking for is if first name contains/have any digit between (0-9).
This is my query , but I am not getting the expected result. 
Select Id, LastName, FirstName, City, Country, Email, IsActive, IsPortalEnabled FROM User WHERE IsActive = True AND IsPortalEnabled = True AND FirstName like '[0-9]%'
Prafull G.Prafull G.
Nidhi - try using
AND (FirstName LIKE '1%' OR Name LIKE '2%' OR...)
 
Prafull G.Prafull G.
Select Id, LastName, FirstName, City, Country, Email, IsActive, IsPortalEnabled FROM User WHERE IsActive = True AND IsPortalEnabled = True AND (FirstName LIKE '0%' OR Name LIKE '1%' OR FirstName LIKE '2%' OR Name LIKE '3%' OR FirstName LIKE '4%' OR Name LIKE '5%' OR FirstName LIKE '6%' OR Name LIKE '7%' OR FirstName LIKE '8%' OR Name LIKE '9%')

It looks ugly, but works fine.
This was selected as the best answer
Andrew GAndrew G
Only thought might be that the query using FirstName LIKE '1%' would return 1Andrew, but not return Andrew1 or And1rew

You may need to consider FirstName LIKE '%1%'

regards
Andrew
James DornJames Dorn
I'm not sure, but you can try Skyvia Query (https://skyvia.com/query/) as an alternative solution.