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

CONCAT( string_value1, string_value2 [, string_valueN ] )

CONCAT( string_value1, string_value2 )

SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) "Full name"
FROM customers 
WHERE First_name LIKE 'R%'

SELECT CONCAT(FIRST_NAME,CONCAT(' ',LAST_NAME)) "Full name"
FROM customers 
WHERE First_name LIKE 'R%'

LEN() function

LEN(str)

SELECT CONCAT(FIRST_NAME,' name has length of:',LEN(FIRST_NAME)) "Info"
FROM customers 
WHERE First_name LIKE '%a%'

LENGTH() function

LENGTH(str)

SELECT CONCAT(FIRST_NAME,CONCAT(' name has length of:',LENGTH(FIRST_NAME))) "Info"
FROM customers 
WHERE First_name LIKE '%a%'

LOWER() function

LOWER(str)

SELECT LOWER(FIRST_NAME) AS Customer
FROM Customers; 

LTRIM() function

LTRIM ( character_expression ) 

LTRIM ( character_expression [, set] )
/* removes all of the characters contained in set*/ 

SELECT CONCAT('   ',LAST_NAME) "NO LTRIM",
  LTRIM(CONCAT('   ',LAST_NAME)) "LTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

SELECT CONCAT('Name: ',LAST_NAME) "NO LTRIM",
  LTRIM(CONCAT('Name: ',LAST_NAME),'Name: ') "LTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

REPLACE() function

REPLACE ( string_expression , string_pattern , string_replacement ) 

SELECT ORDER_ID,
CONCAT(REPLACE(Order_description,' ','_'),'.pdf') "pdf file"
FROM Orders 

RTRIM() function

RTRIM ( character_expression ) 

RTRIM ( character_expression [, set] )
/* removes all of the characters contained in set*/ 

SELECT CONCAT(LAST_NAME,'   ') "NO RTRIM",
  RTRIM(CONCAT(LAST_NAME,'   ')) "RTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

SELECT CONCAT(LAST_NAME,' in action') "NO RTRIM",
  RTRIM(CONCAT(LAST_NAME,' in action'),' in action') "RTRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

SUBSTR() function or SUBSTRING() function

SUBSTR(string, position [, substring_length ])

SUBSTRING(string, position [, substring_length ])

SUBSTRING(string, position , substring_length )

SELECT FIRST_NAME, LAST_NAME, 
CONCAT(SUBSTR(FIRST_NAME,1,1),SUBSTR(LAST_NAME,1,1)) "Initail"
FROM Customers 
WHERE FIRST_NAME LIKE 'R%'

SELECT FIRST_NAME, LAST_NAME, 
CONCAT(SUBSTRING(FIRST_NAME,1,1),SUBSTRING(LAST_NAME,1,1)) "Initail"
FROM Customers 
WHERE FIRST_NAME LIKE 'R%'

TRIM() function

TRIM ( string_expression ) 

TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character }
       FROM ] trim_source)

TRIM([ leading | trailing | both] [characters] 
      from string)

SELECT CONCAT('   ',CONCAT(LAST_NAME,'   ')) "NO TRIM",
  TRIM(CONCAT('   ',CONCAT(LAST_NAME,'   '))) "TRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

SELECT CONCAT('   ',CONCAT(LAST_NAME,'   ')) "NO TRIM",
  TRIM(BOTH ' ' FROM CONCAT('   ',CONCAT(LAST_NAME,'   '))) "TRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

SELECT CONCAT('   ',LAST_NAME,'   ') "NO TRIM",
  RTRIM(LTRIM(CONCAT('   ',LAST_NAME,'   '))) "TRIM"
FROM Customers 
WHERE LAST_NAME LIKE '%e%'

UPPER() function

UPPER(str)

SELECT UPPER(FIRST_NAME) AS Customer
FROM Customers;