CODEWITHSUNDEEP
sqlpostgresqlpgadmin
woodbine
woodbine

Reputation: 662

SQL returns null when like criteria contains more than one character

I'm using SQL to try and return a subset of entries from a table with 3.5m rows using PGAdmin. The following code returns 131,970 rows:

SELECT 
  orgs.org_name, 
  orgs.org_oid
FROM 
  public.orgs
WHER
  org_name LIKE 'L%'
ORDER BY
  orgs.org_name ASC

However, once I add more characters into the LIKE criteria, PGAdmin returns no data at all:

SELECT 
  orgs.org_name, 
  orgs.org_oid
FROM 
  public.orgs
WHER
  org_name LIKE 'Lo%'
ORDER BY
  orgs.org_name ASC

I don't think there's anything wrong with my SQL, but I'm getting no error messages from PGAdmin to indicate that memory or anything else might be the problem.

I'm running on a mac OS X 10.8 using PGAdmin 1.16.0 and Postgres 9.2

Upvotes: 2

Views: 872

Answers (3)

Houari
Houari

Reputation: 5621

You can use ilike to filter without case sensitivity:

SELECT orgs.org_name, orgs.org_oid FROM public.orgs
WHERE
UPPER(org_name) ILIKE 'LO%' --OR ILIKE 'lo%' (same thing)

Upvotes: 2

Robert
Robert

Reputation: 25753

If you sure in orgs you have names start with 'Lo', try to use UPPER as below:

SELECT 
  orgs.org_name, 
  orgs.org_oid
FROM 
  public.orgs
WHERE
  UPPER(org_name) LIKE 'LO%'
ORDER BY
  orgs.org_name ASC

Upvotes: 2

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

Try using lower keyword for the data:

SELECT 
  orgs.org_name, 
  orgs.org_oid
FROM 
  public.orgs
WHER
  lower(org_name) LIKE 'lo%'
ORDER BY
  orgs.org_name ASC

Upvotes: 2

Related Questions