SQL String Functions

SQL built-in String functions

Summary

  • String functions perform manipulation of text that normally are contained in a table column.
  • There is a differentiated range of built-in string functions in the various RDBMS, yet are there some functions which have the same name and executes the same.
  • There are also functions that perform the same but does not have the same name in different RDBMS.
  • Here is a collection of the most important built-in string functions:
    Function RDBMS Description
    CONCAT() SQL server, MySQL, Oracle, PostgreSQL Returns a string that is the result of concatenating two or more string values.
    Oracle takes only two string values.
    LEN() SQL server Returns the length of a string, measured in bytes.
    LENGTH() MySQL, Oracle, PostgreSQL
    LOWER() SQL server, MySQL, Oracle, PostgreSQL Returns a character expression after converting uppercase character data to lowercase.
    LTRIM() SQL server, MySQL, Oracle, PostgreSQL Returns a character expression after it removes leading blanks.
    Oracle and PostgreSQL has an additional parameter that specifies what you want to remove (the default is leading blanks).
    REPLACE() SQL server, MySQL, Oracle, PostgreSQL Replaces all occurrences of a specified string value with another string value.
    RTRIM() SQL server, MySQL, Oracle, PostgreSQL Returns a character string after truncating all trailing blanks.
    Oracle and PostgreSQL has an additional parameter that specifies what you want to remove (the default is trailing blanks).
    SUBSTR() MySQL, Oracle, PostgreSQL Returns a part of a string expression.
    SUBSTRING() SQL server, MySQL, PostgreSQL
    TRIM() MySQL, Oracle Returns a character string after truncating all leading blanks and trailing blanks.
    Oracle and PostgreSQL has additionally specifying what you want to remove leading, trailing or both (default is both and blanks).
    With SQL server you have to use a combination of LTRIM() and RTRIM().
    UPPER() SQL server, MySQL, Oracle Returns a character expression with lowercase character data converted to uppercase.

CONCAT() function

Returns a string that is the result of concatenating two or more string values.
Oracle takes only two string values.
SQL server, MySQL and PostgreSQL Function syntax:
CONCAT( string_value1, string_value2 [, string_valueN ] )
Oracle Function syntax:
CONCAT( string_value1, string_value2 )
CONCAT Example1:
SQL server, MySQL, PostgreSQL:
SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) "Full name"
FROM customers 
WHERE First_name LIKE 'R%'
CONCAT Example2:
SQL server, MySQL, PostgreSQL, Oracle :
SELECT CONCAT(FIRST_NAME,CONCAT(' ',LAST_NAME)) "Full name"
FROM customers 
WHERE First_name LIKE 'R%'
The result should be for both examples:
Full name
Ricard Nixon
Robert Redford

LEN() function

Returns the length of a string, measured in bytes in SQL server.
Function syntax:
LEN(str)
LEN Example:
SQL server :
SELECT CONCAT(FIRST_NAME,' name has length of:',LEN(FIRST_NAME)) "Info"
FROM customers 
WHERE First_name LIKE '%a%'
The result should be:
Info
Ricard name has length of:6
Sandra name has length of:6
Marcy name has length of:5

LENGTH() function

Returns the length of a string, measured in bytes in MySQL, PostgreSQL and Oracle.
Function syntax:
LENGTH(str)
LENGTH Example:
MySQL, Oracle, PostgreSQL :
SELECT CONCAT(FIRST_NAME,CONCAT(' name has length of:',LENGTH(FIRST_NAME))) "Info"
FROM customers 
WHERE First_name LIKE '%a%'
The result should be:
Info
Ricard name has length of:6
Sandra name has length of:6
Marcy name has length of:5

LOWER() function

Returns a character expression after converting uppercase character data to lowercase.
Function syntax:
LOWER(str)
LOWER Example:
SQL server, MySQL, Oracle, PostgreSQL :
SELECT LOWER(FIRST_NAME) AS Customer
FROM Customers; 
The result should be:
CUSTOMER
ricard
robert
judith
sandra
marcy

LTRIM() function

Returns a character expression after it removes leading blanks.
Oracle has an additional parameter that specifies what you want to remove (the default is leading blanks).
SQL server, MySQL Function syntax:
LTRIM ( character_expression ) 
Oracle and PostgreSQL Function syntax:
LTRIM ( character_expression [, set] )
/* removes all of the characters contained in set*/ 
LTRIM Example1:
SQL server, MySQL, Oracle, PostgreSQL:
SELECT CONCAT('   ',LAST_NAME) "NO LTRIM",
  LTRIM(CONCAT('   ',LAST_NAME)) "LTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
The result should be:
NO LTRIM LTRIM
   Redford Redford
   Emerson Emerson
LTRIM Example2:
Oracle, PostgreSQL:
SELECT CONCAT('Name: ',LAST_NAME) "NO LTRIM",
  LTRIM(CONCAT('Name: ',LAST_NAME),'Name: ') "LTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
The result should be:
NO LTRIM LTRIM
Name: Redford Redford
Name: Emerson Emerson

REPLACE() function

Replaces all occurrences of a specified string value with another string value.
Function syntax:
REPLACE ( string_expression , string_pattern , string_replacement ) 
REPLACE Example:
SQL server,MySQL, Oracle, PostgreSQL :
SELECT ORDER_ID,
CONCAT(REPLACE(Order_description,' ','_'),'.pdf') "pdf file"
FROM Orders 
The result should be:
ORDER_ID pdf file
70001 Books_for_studie.pdf
70002 Christmas_sale_.pdf
70003 Teaching.pdf

RTRIM() function

Returns a character string after truncating all trailing blanks.
Oracle has an additional parameter that specifies what you want to remove (the default is trailing blanks).
SQL server, MySQL Function syntax:
RTRIM ( character_expression ) 
Oracle and PostgreSQL Function syntax:
RTRIM ( character_expression [, set] )
/* removes all of the characters contained in set*/ 
RTRIM Example1:
SQL server, MySQL, Oracle, PostgreSQL:
SELECT CONCAT(LAST_NAME,'   ') "NO RTRIM",
  RTRIM(CONCAT(LAST_NAME,'   ')) "RTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
The result should be:
NO RTRIM RTRIM
'Redford   ' 'Redford'
'Emerson   ' 'Emerson'
(The single quotes is not part of the output but used to highlight the results)
RTRIM Example2:
Oracle, PostgreSQL:
SELECT CONCAT(LAST_NAME,' in action') "NO RTRIM",
  RTRIM(CONCAT(LAST_NAME,' in action'),' in action') "RTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
The result should be:
NO RTRIM RTRIM
Redford in action Redford
Emerson in action Emerson

SUBSTR() function or SUBSTRING() function

Returns a part of a string expression..
MySQL, PostgreSQL and Oracle function syntax:
SUBSTR(string, position [, substring_length ])
MySQL and PostgreSQL alternative function syntax:
SUBSTRING(string, position [, substring_length ])
SQL server function syntax:
SUBSTRING(string, position , substring_length )
SUBSTR Example1:
MySQL, PostgreSQL, Oracle :
SELECT FIRST_NAME, LAST_NAME, 
CONCAT(SUBSTR(FIRST_NAME,1,1),SUBSTR(LAST_NAME,1,1)) "Initail"
FROM Customers 
WHERE FIRST_NAME LIKE 'R%'
SUBSTRING Example2:
MySQL, PostgreSQL, SQL server :
SELECT FIRST_NAME, LAST_NAME, 
CONCAT(SUBSTRING(FIRST_NAME,1,1),SUBSTRING(LAST_NAME,1,1)) "Initail"
FROM Customers 
WHERE FIRST_NAME LIKE 'R%'
The result should be for both examples:
FIRST_NAME LAST_NAME Initial
Ricard Nixon RN
Robert Redford RR

TRIM() function

Returns a character string after truncating all leading blanks and trailing blanks.
Oracle and PostgreSQL has additionally specifying what you want to remove leading, trailing or both (default is both and blanks).
With SQL server you have to use a combination of LTRIM() and RTRIM().
MySQL Function syntax:
TRIM ( string_expression ) 
Oracle Function syntax:
TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character }
       FROM ] trim_source)
PostgreSQL Function syntax:
TRIM([ leading | trailing | both] [characters] 
      from string)
TRIM Example1:
Oracle, MySQL, PostgreSQL:
SELECT CONCAT('   ',CONCAT(LAST_NAME,'   ')) "NO TRIM",
  TRIM(CONCAT('   ',CONCAT(LAST_NAME,'   '))) "TRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
TRIM Example2:
Oracle, PostgreSQL:
SELECT CONCAT('   ',CONCAT(LAST_NAME,'   ')) "NO TRIM",
  TRIM(BOTH ' ' FROM CONCAT('   ',CONCAT(LAST_NAME,'   '))) "TRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
Example 3 is a solution to SQL Server to obtain TRIM function:
With SQL server we use a combination of LTRIM and RTRIM :
SELECT CONCAT('   ',LAST_NAME,'   ') "NO TRIM",
  RTRIM(LTRIM(CONCAT('   ',LAST_NAME,'   '))) "TRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'
The result should be for all three examples:
NO TRIM TRIM
'   Redford   ' 'Redford'
'   Emerson   ' 'Emerson'
(The single quotes is not part of the output but used to highlight the results)

UPPER() function

Returns a character expression with lowercase character data converted to uppercase.
Function syntax:
UPPER(str)
UPPER Example:
SQL server,MySQL, Oracle, PostgreSQL :
SELECT UPPER(FIRST_NAME) AS Customer
FROM Customers; 
The result should be:
CUSTOMER
RICARD
ROBERT
JUDITH
SANDRA
MARCY
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.