You are here: Reference Information > SQL Reference Information > SQL Functions and Operations > SQL String Functions and Operators

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 Return Type Description Example Result
string1 || string2 [ || string_n ]... STRING

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

Note: In previous versions, NuoDB also allowed concatenation of strings with the single pipe "|". This is no longer allowed and NuoDB will return a message suggesting that perhaps the user meant to use "||".

'NuoDB,'||' Inc.'

'Value: ' || 42

NuoDB, Inc.

Value: 42
string1 [ white_space ] string2 [ [ white_space ] string_n ...] STRING string1 concatenated with string2, optionally concatenated with n more strings. Each string value must be enclosed by single quotes, including numeric values. 'NuoDB,' 'Inc.'

'Value: ' '42'

NuoDB, Inc.

Value: 42

BIT_LENGTH(string) INTEGER Number of bits in string bit_length('NuoDB') 40
CHAR(integer) STRING Takes an integer utf-32 code point in the range 0 - 0x10FFFF and returns a UTF-8 encoded unicode character string.

char(33)

unicode(char(33))

char(unicode('!'))

!

33

!

CHAR_LENGTH(string)
CHARACTER_LENGTH ( string )
INTEGER Number of characters in string char_length('NuoDB') 5
CHAR_LENGTH(string USING OCTETS)
CHARACTER_LENGTH (string USING OCTETS)
INTEGER Number of bytes in string char_length('NuoDB' using octet) 5
CONCAT(string1 [, string2 [, string_n ]... ]) STRING string1 concatenated with string2, concatenated with an optional number of n strings concat('abcde', 2, 22)

concat('abcde', 2, NULL, 22)
abcde222

NULL
LCASE(string) STRING Convert string to lower case lcase('NuoDB') nuodb
LEFT( string, n ) STRING Returns first n characters in the string string. n must be a positive number. left('abcde', 3) abc
LENGTH(string) INTEGER Number of characters in string length('NuoDB') 5
LOCATE(string1, string2 [ , start_pos ]) INTEGER Location of specified string1 in string2. Optionally, begin looking for string1 at start_pos. Returns 0 if string1 is not present in string2. locate('c','abcxyz')

locate('xyz','abcxyz')
3

4
LOWER(string) STRING Convert string to lower case lower('NuoDB') nuodb
LTRIM(string) STRING Removes leading spaces from the start of string ltrim(' abcd ') 'abcd '
OCTET_LENGTH(string) INTEGER Number of bytes in string octet_length('NuoDB') 5
POSITION(string1 IN string2) INTEGER Location of specified string1 in string2 position('bridge' in 'Cambridge') 4
QUOTENAME(string1, string2) STRING

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: 

`   '   "   (   )   [   ]   {   }   <   >

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.

quotename('abc[]def') [abc[]]def]
REPLACE(string, from_text, to_text) STRING Replace in string all occurrences of from_text with to_text replace('abcabc','bc','f') afaf
REPLICATE(string, n) STRING Returns string repeated n number of times. replicate('foo', 5) foofoofoofoofoo
REVERSE(string) STRING Returns reversed string reverse('abcde') edcba
RIGHT( string , n ) STRING Returns last n characters in the string string. n must be a positive number. right('abcde', 3) cde
RTRIM(string) STRING Removes trailing spaces from the end of string rtrim(' abcd '); ' abcd'
SUBSTRING( string FROM start_pos [ FOR length ] )
SUBSTR( string FROM start_pos [ FOR length ])
STRING Returns a subset of string starting from start_pos if specified, optionally for length substring('Thomas' from 2 for 3) hom
SUBSTRING( string, start_pos [ ,length ] )
SUBSTR(string, start_pos [ ,length ])
STRING Returns a subset of string starting from start_pos, optionally for length substring('alphabet',3,2) ph
SUBSTRING_INDEX(string1,string2,count)
SUBSTR_INDEX(string1,string2,count)
STRING 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. select substring_index (' www.nuodb.com' , '.' ,2) from dual;

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

nuodb.com
TRIM([ LEADING | TRAILING | BOTH ] [string1] FROM string2) STRING 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. trim(both 'x' from 'xTomxx')

trim(leading 'x' from 'xTomxx')
Tom

Tomxx
UCASE(string) STRING Convert string to upper case ucase('NuoDB') NUODB
UNICODE(string) INTEGER Return the integer value, as defined by the Unicode standard, for the first character of the input expression string. unicode('Āā') 256
UPPER(string) STRING Convert string to upper case upper('NuoDB') NUODB

Examples

The following are examples of using several of the above string functions in a SELECT statement.

Example 1

Following is an example of the QUOTENAME() function:

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

Using functions in the GROUP BY and ORDER BY clauses.

SELECT EXTRACT(year FROM CURRENT_DATE) - birthyear AS AGE,
       COUNT(1) AS number_of_players
  FROM players
 WHERE birthday > 0
 GROUP BY EXTRACT(year FROM CURRENT_DATE) - birthyear 
 ORDER BY EXTRACT(year FROM CURRENT_DATE) - birthyear;
 AGE  NUMBER_OF_PLAYERS 
 ---- ------------------
 20            3        
 21           16        
 22           42        
 23           77        
...

Example 4

UNICODE

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