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 'NuoDB,'||' Inc.' from dual;
    [||]
 -----------
 NuoDB, Inc.

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 'NuoDB,' ' Inc.' from dual;
 ['NuoDB, Inc.']
 ----------------
   NuoDB, Inc.

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 )

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)

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)

Convert string to lower case.

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)

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)

Convert string to lower case.

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)

Number of bytes in string.

Return type: INTEGER

select octet_length('NuoDB') from dual;
 [OCTET_LENGTH]
 ---------------
        5

POSITION(string1 IN string2)

Location of 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 in string all occurrences of from_text with to_text.

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.

In case of longer string, the length of output string is truncated to 1048576 characters.

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, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

Return type: STRING

select substring_index (' www.nuodb.com' , '.' ,2) from dual;
 [SUBSTRING_INDEX]
 ------------------
      www.nuodb

select substr_index (' www.nuodb.com' , '.',-2) from dual;
 [SUBSTR_INDEX]
 ---------------
    nuodb.com

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)

Convert string to upper case.

Return type: STRING

select ucase('NuoDB') from dual;
 [UCASE]
 --------
  NUODB

UNICODE(string)

Return 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)

Convert string to upper case.

Return type: STRING

select upper('NuoDB') from dual;
 [UPPER]
 --------
  NUODB

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

Example 3

UNICODE

SELECT UNICODE('Āā') FROM DUAL;
 [UNICODE]
 ----------
    256

SELECT UNICODE(SUBSTR('abcĀādef',5,1)) FROM DUAL;
 [UNICODE]
 ----------
    257