What are Character Functions in SQL?

Photo of author

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
  • 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 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)


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)

Input:  SELECT UPPER(‘alphabet’) FROM DUAL;


INITCAP(): The initial letter of every word is converted to uppercase using this function, while the remaining letters get changed to lowercase. The string’s words must be separated with a #, _, or space.

Syntax: INITCAP(str)

Input: 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’)

Input: SELECT CONCAT(‘social,’ ‘studies’) FROM DUAL;

Output: socialstudies

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)

Input:  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)

Input: 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 no match gets detected after searching the string, the INSTR function produces 0.

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

Input: 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)

Input: SELECT LPAD(‘computer’, 12, ‘*’) AS LeftPaddedString;

Output: ****computer

Syntax: RPAD(str , len , padstr)

Input: 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)

Input:  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)

Input: SELECT REPLACE(‘political science,’ ‘political,’ ‘computer’) FROM DUAL;

Output: computer science

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

Leave a Comment