O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

POSITION

Synopsis

The POSITION function returns an integer that indicates the starting position of a string within the search string. MySQL and PostgreSQL support the POSITION function with no variation from the SQL99 syntax. PostgreSQL has a synonymous function, TEXTPOS, while MySQL has the synonymous function, LOCATE.

Oracle’s equivalent function is called INSTR. Microsoft SQL Server has both CHARINDEX and PATINDEX. The CHARINDEX and PATINDEX are very similar, except that PATINDEX allows the use of wildcard characters in the search criteria. For example:

/* On MySQL */
SELECT LOCATE('bar', 'foobar');
-> 4

/* On MySQL and PostgreSQL */
SELECT POSITION('fu' IN 'snafhu');
-> 0

/* On Microsoft SQL Server */
SELECT CHARINDEX( 'de', 'abcdefg' )
GO
-> 4
SELECT PATINDEX( '%fg', 'abcdefg' )

GO
-> 6

String Functions

Basic string functions offer a number of capabilities and return a string value as a result set. Some string functions are dyadic, indicating that they operate on two strings at once. SQL99 supports the string functions listed in Table 4.6.

Table 4-6. SQL String Functions

Function

Usage

CONCATENATE(expression || expression)

Appends two or more literal expressions, column values, or variables together into one string.

CONVERT

Converts a string to a different representation within the same character set.

LOWER

Converts a string to all lowercase characters.

SUBSTRING

Extracts a portion of a string.

TRANSLATE

Converts a string from one character set to another.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required