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

string1 || string2 [ || string_n ]…

string1 concatenated with string2, concatenated with an optional number of n strings. The string value can be numeric, such as integer or decimal.

Return type: STRING

SELECT 'Hello '||'World!' FROM dual;

    [||]
 -----------
 Hello World!

SELECT 'Value: ' || 42 FROM dual;

   [||]
 ---------
 Value: 42

string1 [ white_space ] string2 [ [ white_space ] string_n …]

string1 concatenated with string2, optionally concatenated with n more strings. Each string value must be enclosed by single quotes, including numeric values.

Return type: STRING

SELECT 'Hello ' 'World!' FROM dual;

 ['Hello World!']
 ----------------
   Hello World!

SELECT 'Value: ' '42' FROM dual;

 ['Value: 42']
 --------------
   Value: 42

BIT_LENGTH(string)

Number of bits in string.

Return type: INTEGER

SELECT bit_length('NuoDB') FROM dual;

 [BIT_LENGTH]
 -------------
      40

CHAR(integer)

Takes an integer UTF-32 code point in the range 0–0x10FFFF and returns a UTF-8 encoded unicode character string.

Return type: STRING

SELECT char(33), unicode(char(33)), char(unicode('!')) FROM dual;

 [CHAR]  [UNICODE]  [CHAR]
 ------- ---------- -------
    !        33        !

CHAR_LENGTH(string)
CHARACTER_LENGTH( string )

Returns the number of characters in string.

Return type: INTEGER

SELECT char_length('NuoDB') FROM dual;

 [CHAR_LENGTH]
 --------------
       5

CHAR_LENGTH(string USING OCTETS)
CHARACTER_LENGTH(string USING OCTETS)

Returns the number of bytes in string.

Return type: INTEGER

SELECT char_length('NuoDB' USING OCTETS) FROM dual;

 [OCTET_LENGTH]
 ---------------
        5

CONCAT(string1 [, string2 [, string_n ]… ])

string1 concatenated with string2, concatenated with an optional number of n strings.

Return type: STRING

SELECT concat('abcde', 2, 22) FROM dual;

 [CONCAT]
 ---------
 abcde222

SELECT concat('abcde', 2, NULL, 22) FROM dual;

 [CONCAT]
 ---------
  <null>

LCASE(string)

Converts string to lowercase.

Return type: STRING

SELECT lcase('NuoDB') FROM dual;

 [LCASE]
 --------
  nuodb

LEFT( string, n )

Returns first n characters in the string string. n must be a positive number.

Return type: STRING

SELECT left('abcde', 3) FROM dual;

 [LEFT]
 -------
   abc

LENGTH(string)

Returns the number of bytes in string.

Return type: INTEGER

SELECT length('©') FROM dual;

 [LENGTH]
 ---------
     2

LOCATE(string1, string2 [ , start_pos ])

Location of specified string1 in string2. Optionally, begin looking for string1 at start_pos. Returns 0 if string1 is not present in string2.

Return type: INTEGER

SELECT locate('c','abcxyz') FROM dual;

 [LOCATE]
 ---------
     3

SELECT locate('xyz','abcxyz') FROM dual;

 [LOCATE]
 ---------
     4

LOWER(string)

Converts string to lowercase.

Return type: STRING

SELECT lower('NuoDB') FROM dual;

 [LOWER]
 --------
  nuodb

LTRIM(string)

Removes leading spaces from the start of string.

Return type: STRING

SELECT ltrim(' abcd ') FROM dual;

 [LTRIM]
 --------
  abcd

OCTET_LENGTH(string)

Returns the number of bytes in string.

Return type: INTEGER

SELECT octet_length('NuoDB') FROM dual;

 [OCTET_LENGTH]
 ---------------
        5

POSITION(string1 IN string2)

Returns the location of the specified string1 in string2.

Return type: INTEGER

SELECT position('bridge' in 'Cambridge') FROM dual;

 [POSITION]
 -----------
      4

QUOTENAME(string1, string2)

Returns a string with delimiters added to make the input string a valid delimited identifier.

string1 is a string of character data.

string2 is a string whose first character is to be used as the delimiter. The delimiter can be one of the following punctuation marks:

`   '   "   (   )   [   ]   {   }   &lt;   &gt;

The returned string contains the delimiter specified by the first character in string2 added to make string1 a valid delimited identifier. This function is for SQL Server compatibility. If string2 is omitted, the default is left square bracket ('[') or right square bracket (']'), which have the same effect. See Examples below.

Return type: STRING

SELECT quotename('abc[]def') FROM dual;

  QUOTENAME
 -----------
 [abc[]]def]

REPLACE(string, from_text, to_text)

Replace all occurrences of from_text with to_text in the specified string.

Return type: STRING

SELECT replace('abcabc','bc','f') FROM dual;

 [REPLACE]
 ----------
    afaf

REPLICATE(string, n)

Returns string repeated n number of times.

Return type: STRING

SELECT replicate('foo', 5) FROM dual;

   [REPLICATE]
 ---------------
 foofoofoofoofoo

Returns NULL if the count parameter n is negative.
The replication will stop when the output string reaches the maximum length of 1048576 bytes.

REVERSE(string)

Returns reversed string.

Return type: STRING

SELECT reverse('abcde') FROM dual;

 [REVERSE]
 ----------
   edcba

RIGHT( string , n )

Returns last n characters in the string string. n must be a positive number.

Return type: STRING

SELECT right('abcde', 3) FROM dual;

 [RIGHT]
 --------
   cde

RTRIM(string)

Removes trailing spaces from the end of string.

Return type: STRING

SELECT rtrim(' abcd ') FROM dual;

 [RTRIM]
 --------
   abcd

SUBSTRING( string FROM start_pos [ FOR length ] )
SUBSTR( string FROM start_pos [ FOR length ])

Returns a subset of string starting from start_pos if specified, optionally for length.

Return type: STRING

SELECT substring('Thomas' FROM 2 for 3) FROM dual;

 [SUBSTR]
 ---------
    hom

SUBSTRING( string, start_pos [ , length ] )
SUBSTR(string, start_pos [ , length ])

Returns a subset of string starting from start_pos, optionally for length.

Return type: STRING

SELECT substring('alphabet',3,2) FROM dual;

 [SUBSTR]
 ---------
    ph

SUBSTRING_INDEX(string1, string2, count)
SUBSTR_INDEX(string1, string2, count)

Returns the substring from string1 before count occurrences of the delimiter string2 . If count is positive and string1 has a few occurrences of the delimiter string2, everything to the left of the final delimiter (counting from the left) is returned. If count is negative and string1 has a few occurrences of the delimiter string2, everything to the right of the final delimiter (counting from the right) is returned.
If count is positive and string1 has no occurrences of the delimiter string2, string1 is returned. If count is negative and string1 has no occurrences of the delimiter string2, string1 is returned.
If count is 0, an empty string is returned.

Return type: STRING

SELECT substring_index('three : delimiters : in the : string',':' ,2) FROM dual;

 [SUBSTRING_INDEX]
 ------------------
 three : delimiters

SELECT substr_index('four : delimiters : in : the : string',':',-2) FROM dual;

 [SUBSTR_INDEX]
 ---------------
  the : string

SELECT substr_index('three : delimiters : in the : string',':',0) FROM dual;

 [SUBSTR_INDEX]
 ---------------

SELECT substr_index('no delimiters in the string',':',0) FROM dual;

 [SUBSTR_INDEX]
 ---------------

SELECT substr_index('no delimiters in the string',':',1) FROM dual;

 [SUBSTR_INDEX]
 ---------------
no delimiters in the string

TRIM([ LEADING | TRAILING | BOTH] [string1] FROM string2)

Remove the longest substring containing only string1 (a space by default) from the LEADING/TRAILING/BOTH ends of string2. If [LEADING | TRAILING| BOTH] is omitted, BOTH is assumed.

Return type: STRING

SELECT trim(both 'x' FROM 'xTomxx') FROM dual;

 [TRIM]
 -------
   Tom

SELECT trim(leading 'x' FROM 'xTomxx') FROM dual;

 [TRIM]
 -------
  Tomxx

UCASE(string)

Converts string to uppercase.

Return type: STRING

SELECT ucase('NuoDB') FROM dual;

 [UCASE]
 --------
  NUODB

UNICODE(string)

Returns the integer value, as defined by the Unicode standard, for the first character of the input expression string.

Return type: INTEGER

SELECT unicode('Āā') FROM dual;

 [UNICODE]
 ----------
    256

UPPER(string)

Converts string to uppercase.

Return type: STRING

SELECT upper('NuoDB') FROM dual;

 [UPPER]
 --------
  NUODB

Examples

The following examples use several of the earlier mentioned string functions in a SELECT statement.

Example 1

QUOTENAME()

SELECT quotename('abc[]def') FROM dual;

 [QUOTENAME]
 ------------
 [abc[]]def]

SELECT quotename('hello','"') FROM dual;

 [QUOTENAME]
 ------------
    "hello"

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

Example 3

UNICODE

SELECT unicode('Āā') FROM dual;

 [UNICODE]
 ----------
    256

SELECT unicode(substr('abcĀādef',5,1)) FROM dual;

 [UNICODE]
 ----------
    257