String Functions | DATABASE

Introduction

SQL built in string functions make it possible for you to find and alter text values. Syntax of the string functions can vary for different database systems. Commonly used string functions are

• CONCAT()
• UPPER()
• LOWER()
• TRIM()
• LTRIM()
• RTRIM()
• SUBSTRING()
• LENGTH()
• STRCMP()

Concatenation

String concatenation means to append one string to the end of another string. Concatenation can be used to join strings from different sources including column values, literal strings etc. To join two or more strings into one, you use the CONCAT() function with the following syntax:

CONCAT ( input_string1, input_string2 [, input_stringN ] );

The following example concatenate three literal string.

SELECT  CONCAT('Hello', ' ', 'World') AS str;

/* Result */
Hello World

Below statement concatenate values in the first_name and last_name columns of the employleeinfo table:

SELECT firstName, lastName, CONCAT(firstName, ' ', lastName) AS FullName FROM employleeinfo

/*Query Output */
firstName lastName  FullName
Rahul     Bhagwat   Rahul Bhagwat
Stephen   Fleming   Stephen Fleming
Sebastian Smith     Sebastian Smith
New Name  Sharma    New Name Sharma
Erica     Edwards   Erica Edwards
Priya     Gowda     Priya Gowda
Shekar    Chandra   Shekar Chandra
Garima    Singh     Garima Singh
Rahul     Singh     Rahul Singh

Case Conversion

UPPER() function converts an input string into uppercase. LOWER() function converts a string into lowercase. Syntax of these functions are

UPPER(input_string)
LOWER(input_string)

The following statement uses above function to convert the first and last names of employee.

SELECT firstName, lastName, UPPER(firstName) AS UpperCase, LOWER(lastName) AS LowerCase FROM employleinfo

/*Query Result */
firstName lastName  UpperCase LowerCase
Rahul     Bhagwat   RAHUL     bhagwat
Stephen   Fleming   STEPHEN   fleming
Sebastian Smith     SEBASTIAN smith
New Name  Sharma    NEW NAME  sharma
Erica     Edwards   ERICA     edwards
Priya     Gowda     PRIYA     gowda
Shekar    Chandra   SHEKAR    chandra
Garima    Singh     GARIMA    singh
Rahul     Singh     RAHUL     singh

Space Trimming

LTRIM() function returns a string after removing leading blanks. TRIM() function removes spaces or specified characters from both ends of a string. RTRIM() function returns a string after truncating all trailing blanks.

TRIM([removed_characters FROM] input_string)
LTRIM(input_string)
RTRIM(input_string)

removed_characters contains characters which will be removed from input_string. It is an optional argument. If you skip it, the TRIM() function will return a string after truncating all leading and trailing spaces from the input_string.

SELECT LTRIM('   Hello World   ') AS LeftTrim
SELECT RTRIM('   Hello World   ') AS RightTrim
SELECT TRIM('   Hello World   ') AS Trim

/* Result */
Hello World   
   Hello World
Hello World

Using TRIM() function to remove specified characters from both sides of a string.

SELECT TRIM('+' FROM '+++Hello World+++') result

/* Result */
Hello World

Substring

SUBSTRING() extracts a substring with a specified length starting from a location in an input string.

SUBSTRING(input_string, start, length);

• start is an integer that specifies the location where the returned substring starts. Note that the first character in the input_string is 1, not zero.
• length is a positive integer that specifies the number of characters of the substring to be returned. It is an optional parameter.

This example uses the SUBSTRING() function to extract firstName and lastName from fullName of candidates.

SELECT
  fullname,
  SUBSTRING(fullname, 1, POSITION(' ' IN fullname) - 1) AS firstName,
  SUBSTRING(fullname, POSITION(' ' IN fullname) + 1) AS LastName
FROM candidates

/* Result */
fullname        firstName LastName
John Doe        John      Doe
Lily Bush       Lily      Bush
Peter Drucker   Peter     Drucker
Jane Doe        Jane      Doe
Rahul Bhagwat   Rahul     Bhagwat
Stephen Fleming Stephen   Fleming
Shekar Chandra  Shekar    Chandra

String Length

LENGTH function returns the number of characters in a string. Some database systems use the LEN function that has the same effect as the LENGTH function. Syntax of this function is

LENGTH(string)

If the input string is empty, the LENGTH returns 0. It returns NULL if the input string is NULL. The following statement sorts the candidate names based on length.

 SELECT
  fullname,
  LENGTH(fullname) as Length
FROM candidates
ORDER BY Length DESC

/* Query output */
fullname        Length
Stephen Fleming 15
Shekar Chandra  14
Peter Drucker   13
Rahul Bhagwat   13
Lily Bush       9
John Doe        8
Jane Doe        8

Comparison

STRCMP() used to compare two strings according to their sort order. It returns either 1, -1, or 0, depending on whether the first string is larger, smaller, or the same size as the second string, according to sort order.

STRCMP(string1, string2)

Reference

String Functions