String Operators

I l @ ve RuBoard

Essentially, all string operators return implicit Boolean true or false values given the supplied comparison. (The exception is the concatenation operators shown in the next "Listing" section.)

When making comparisons, the characters ' location in the ANSI chart is taken into account. Therefore, a lowercase "a" is seen as less than (<) an uppercase "A."

String operators make use of regular expression matching. This expression matching includes an internal format and a POSIX-compliant format.

PostgreSQL can make use of two distinct types of pattern matching: an ANSI-SQL method and a POSIX regex style. The internal ANSI-SQL style makes use of the LIKE and NOTLIKE keywords. This ANSI-SQL method can use the following wildcards for pattern matching:

Wildcard

Meaning

%

Any matching character

_

Any single character

Conversely, the POSIX-compliant operators use the standard regex comparisons, such as the following:

POSIX Regex Symbol

Meaning

.

Single character match.

*

Any string of 1 or more.

+

Repetition of a sequence.

?

Possible repetition of a sequence.

[ ]

List of single characters enclosed in brackets. Matches any of those.

^[ ]

List of single characters enclosed in brackets. Rejects matches to any of those.

etc

A full discussion of POSIX-style regular expressions is beyond the scope of this book. See the man pages for sed , awk , and egrep for more information on POSIX-style regex.

The regex engine included with most versions of PostgreSQL is the POSIX 1003.2 "egrep" style. This regex library, by Henry Spencer, is included in many other popular applications. More information on the regex engine included in a specific version of PostgreSQL can usually be found in the source directory $ SOURCE/backend/ regex .

Listing

<

Less than

<=

Less than or equal to

<>

Not equal

=

Equal to

>

Greater than

>=

Greater than or equal to

Concatenate strings

!!=

Not like

~~

Like

LIKE

Like

ILIKE

Like; case insensitive

NOT ILIKE

Not like; case insensitive

NOT LIKE

Not like

!~~

Not like

~

Match using regex; case sensitive

~*

Match using regex; case insensitive

!~

No match using regex; case sensitive

!~*

No match using regex; case insensitive

Notes/Examples

Select all records from a table where the first name is Bob :

 SELECT * FROM authors WHERE firstname='Bob'; 

Select all records from a table, except those named Bob :

 SELECT * FROM authors WHERE firstname<>'Bob'; 

Select all records where the first name begins with Bo :

 SELECT * FROM authors WHERE firstname LIKE 'Bo'; 

Select all records where the first name begins with b , regardless of case:

 SELECT * FROM authors WHERE firstname ILIKE 'b'; 
I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net