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
-> 6String 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. |