Skip site navigation (1) Skip section navigation (2)

Re: using position in where

From: John DeSoi <desoi(at)pgedit(dot)com>
To: Lynn Manhart <ManhartL(at)mstarmetro(dot)net>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: using position in where
Date: 2009-11-13 03:57:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Nov 12, 2009, at 7:29 PM, Lynn Manhart wrote:

> select * in customers where position ('sub_string' in 'text_column')  
> > 0;

If you really have single quotes around the name of your text column,  
position is looking for your string in that literal text. It should  
not have single quotes. If your column name is a reserved word or has  
special characters you can double quote it.

I also think you want select * "FROM" not "IN", so

SELECT * FROM customers WHERE position('sub_string' in  
customers_text_column) > 0;

> Another question - how are upper and lower case handled when using  
> "order by"? In my experimenting, it seems to be doing a case  
> insensitive compare, but the docs I've read seem to indicate  
> otherwise.

It depends on the locale settings when the cluster was created. On US  
systems, typically uppercase precedes lowercase. E.g. "Zip" is before  
"apple" in an ascending sort.

John DeSoi, Ph.D.

In response to

pgsql-novice by date

Next:From: Jasen BettsDate: 2009-11-16 07:50:51
Subject: Re: using position in where
Previous:From: Lynn ManhartDate: 2009-11-13 00:29:18
Subject: using position in where

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group