Character Functions in SQL: Manipulating Text in Databases

In this article, we will discuss the character functions in SQL. A function that accepts several characters or numbers called parameters and generates a character value is called a character function or string function.

The string functions return a string value; as a result set, they also contain many features. SQL offers the following character data types:

• CHAR
• VARCHAR
• VARCHAR2 
• LONG
• RAW
• LONG RAW

With the help of a comprehensive range of character functions provided by SQL, you may learn more about strings and change their contents in various ways. The two categories of character functions are as follows:

Case-Manipulative: LOWER, UPPER, and INITCAP

Character-Manipulative: CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM, and REPLACE

Character Functions in SQL

Case-Manipulative Functions

Lower():

Lowercase alpha character values get converted using this function. If the input string is of a fixed length, that length of a string will be returned by LOWER.  Since the case is unimportant for numerals and special characters like the dollar symbol ($) or modulus (%), LOWER will not affect any characters within the string which are not letters.

Syntax: LOWER(str)

SELECT LOWER('ALPHABET') FROM DUAL;

Output: alphabet

Upper():

This function raises the case of alpha character values. If the entering string is fixed-length, the UPPER function will also return a fixed-length string. Since the case is unimportant for numerals and special characters like the dollar symbol ($) or modulus (%), UPPER will not alter any characters within the string which are not letters.

Syntax: UPPER(str)

SELECT UPPER('alphabet') FROM DUAL;

Output: ALPHABET

INITCAP():

The initial letter of every word is converted to uppercase using this function, while the remaining letters get changed to lowercase. You can separate the string’s words with a #, _, or space.

Syntax: INITCAP(str)

SELECT INITCAP('sharukh is watching television') FROM DUAL;

Output: Sharukh Is Watching Television

Character-Manipulative Functions

CONCAT():

String2 is always added (concatenated) to the final segment of string1 using this function. Suppose any of the given strings are NULL, the function outputs input parameters that are not NULL. CONCAT returns NULL if either or both strings are NULL.

 

Syntax: CONCAT(‘str1’ , ‘str2’)

SELECT CONCAT('social,' 'studies') FROM DUAL;

Output: socialstudies

If you encounter database corruption issues, consider using reliable tools such as the SQL database repair tool offered by Stellarinfo.com for efficient recovery and maintenance.

LENGTH():

The total length of the supplied string gets rebounded by this function. The LENGTH function outputs NULL and not Zero if the given input string is NULL. Additionally, the LENGTH function contains the extra spaces and returns the entire length of a string if the given input string has additional spaces around the beginning, middle, or end of the string.

Syntax: LENGTH(str)

SELECT LENGTH('sharukh is watching telivision') FROM DUAL;

Output: 30

SUBSTR():

This function returns the string’s starting and ending characters. When a substring length isn’t specified, SUBSTR returns all characters up to the string’s end, i.e., from the starting position specified.

Syntax: SUBSTR(‘str’ , start-index , length_of_extracted_string)

SELECT SUBSTR('sharukh is watching television', 8) FROM DUAL;

Output: is watching television

INSTR():

This function returns the character or string’s numerical location within the specified string. You can also select a starting point of m and a string’s occurrence number, n. Additionally, if no starting point is determined, the search is automatically initiated from index 1. If there is no match after searching the string, the INSTR function produces 0.

Syntax: INSTR(‘str1’ , ‘str2’)

SELECT INSTR('Shahrukh is watching television, 'is')

AS Found;

Output: 9

LPAD() & RPAD():

These functions return strings that have been left or right-padded (depending on the use), giving rise to the “L” in “LPAD” and the “R” in “RPAD,” respectively. There is also length and pad string given. The input string contains padding on the left or right (depending on the purpose) with spaces if a padded string is not specified.

Syntax: LPAD(str , len , padstr)

SELECT LPAD('computer', 12, '*') AS LeftPaddedString;

Output: ****computer

Syntax: RPAD(str , len , padstr)

SELECT RPAD('computer', 12, '*') AS RightPaddedString;

Output: computer****

TRIM():

The above function trims the supplied string from the beginning or end. (or both). If there is additional space at the beginning or end of a string and no string or character is inclined to get removed, those different characters get removed.

Syntax: TRIM( [characters from] str)

SELECT TRIM('c' FROM 'computer') FROM DUAL;

Output: computer

REPLACE():

This function looks through a character string alone; if detected, it replaces it at all instances with a provided replacement string. The REPLACE function helps find character patterns and alter all occurrences of those patterns at once.

The REPLACE function deletes every instance of that particular character string within the input string if a substitute string is not supplied. REPLACE returns NULL if neither an acceptable nor replacement string is provided.

Syntax:  REPLACE(text, search_str, replacement_str)

SELECT REPLACE('political science,' 'political,' 'computer') FROM DUAL;

Output: computer science

FAQs

What is the LEN function in SQL?

The LEN function in SQL is a character function that returns the length of a string in characters. You can use it with any character data type.

What is the LEFT function in SQL?

The LEFT function in SQL is a character function that returns a specified number of characters from the beginning of a string.

What is the RIGHT function in SQL?

The RIGHT function in SQL is a character function that returns a specified number of characters from the end of a string.

What is the SUBSTRING function in SQL?

The SUBSTRING function in SQL is a character function that returns a portion of a string. You can specify the starting position and length of the substring.

What is the UPPER function in SQL?

The UPPER function in SQL is a character function that converts all characters in a string to uppercase.

What is the LOWER function in SQL?

The LOWER function in SQL is a character function that converts all characters in a string to lowercase.

Summing Up

So this is all about character functions in SQL. This article discusses the types of character functions. We hope this solves your query.

See also, Difference between SQL and T-SQL.