SQL String Functions and Operators
In the following table, string
, string1
, string2
, string_n
, substring
and delim
refer to a string literal in quotes, a literal value that can be converted to a string (such as a number), or column from a table that can be converted to a string. NULL
arguments are not valid and will return NULL
. The variables n
, start_pos
, length
, and count
represent a literal value or column from a table that can be converted to an integer. The variable white_space
is any number of consecutive spaces or tabs.
Function | Description | ||||
---|---|---|---|---|---|
|
|
||||
|
|
||||
BIT_LENGTH( |
Number of bits in string.
|
||||
CHAR( |
Takes an integer utf-32 code point in the range 0 - 0x10FFFF and returns a UTF-8 encoded unicode character string.
|
||||
CHAR_LENGTH( |
Number of characters in string.
|
||||
CHAR_LENGTH( |
Number of bytes in string.
|
||||
CONCAT( |
|
||||
LCASE( |
Convert
|
||||
LEFT( |
Returns first
|
||||
LENGTH( |
Number of bytes in
|
||||
LOCATE( |
Location of specified
|
||||
LOWER( |
Convert
|
||||
LTRIM( |
Removes leading spaces from the start of
|
||||
OCTET_LENGTH( |
Number of bytes in
|
||||
POSITION( |
Location of specified
|
||||
QUOTENAME( |
Returns a string with delimiters added to make the input string a valid delimited identifier.
The returned string contains the delimiter specified by the first character in
|
||||
REPLACE( |
Replace in string all occurrences of
|
||||
REPLICATE( |
Returns
|
||||
REVERSE( |
Returns reversed
|
||||
RIGHT( |
Returns last
|
||||
RTRIM( |
Removes trailing spaces from the end of
|
||||
SUBSTRING( |
Returns a subset of
|
||||
SUBSTRING( |
Returns a subset of
|
||||
SUBSTRING_INDEX( |
Returns the substring from
|
||||
TRIM([ LEADING | TRAILING | BOTH ] [ |
Remove the longest substring containing only
|
||||
UCASE( |
Convert
|
||||
UNICODE( |
Return the integer value, as defined by the Unicode standard, for the first character of the input expression
|
||||
UPPER( |
Convert
|
Examples
The following are examples of using several of the above string functions in a SELECT
statement.
Example 1
QUOTENAME()
SELECT QUOTENAME('abc[]def') FROM dual;
[QUOTENAME]
------------
[abc[]]def]
SELECT QUOTENAME('blah','"') FROM dual;
[QUOTENAME]
------------
"blah"
SELECT QUOTENAME('abc def') FROM dual;
[QUOTENAME]
------------
[abc def]
Example 2
Concatenate and format result set columns. The LENGTH
function is optional. If not used, the SUBSTR
function will return the string starting at position 2 for the remainder of the string.
SELECT UPPER( SUBSTR(firstname,1,1) )||LOWER( SUBSTR(firstname,2,LENGTH(firstname)-1) )||' '
||UPPER( SUBSTR(lastname,1,1) )||LOWER( SUBSTR(lastname,2,LENGTH(lastname)-1) ) AS player_name
,(LASTNHL - FIRSTNHL) +1 AS years_in_nhl
FROM players
WHERE birthstate = 'VT';
PLAYER_NAME YEARS_IN_NHL
------------ -------------
John Leclair 17
Graham Mink 6