SQL in a Nutshell

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.