STRING functions in SQL

STRING functions perform operations on the characters. They are used to perform an operation on the input string and return an output string.

Now let us understand some of the most commonly used basic string functions which are defined in SQL as follows:

  • CONCAT
  • LENGTH
  • SUBSTR
  • REPLACE
  • REVERSE
  • LOWER
  • UPPER
  • INTICAP

CONCAT() function in SQL:

This function joins two strings and retrieves the combined result.

The syntax for the CONCAT() function :

SELECT CONCAT ('string_1','string_2')
FROM table_name;

CONCAT() function in the DUAL table:

Example of the CONCAT() function in the DUAL table :

SELECT CONCAT ('Shruthi','Gowda')
FROM DUAL;

Both names are combined using the CONCAT statement.
concat-function

CONCAT() function in the user-created table:

Example of the CONCAT() function in the user-created table :

SELECT CONCAT (first_name,last_name)
FROM persons_table;

retrieved-data

In this query, the first_name and last_name columns are combined and seen in the output.
concatenated-string

LENGTH() function in SQL:

This function counts the number of characters present in the given input string. The LENGTH() functions can be used in the DUAL table and in the user-created table.

LENGTH() in the DUAL table:

Syntax of the LENGTH() function in the DUAL table :

SELECT LENGTH ('string') FROM table_name;

Example of the LENGTH() function in the DUAL table :

SELECT LENGTH ('hello world!') FROM DUAL;

In the below query, the length of the string is calculated.
length-string

LENGTH() in the user-created table:

Syntax of the LENGTH() function in the created table :

SELECT column_name, LENGTH(column_name)
FROM table_name;

Example of the LENGTH() function in the created table :

SELECT first_name, LENGTH(first_name)
FROM persons_table;

retrieved-data

In the below query, the length of the values present in the first_name column is calculated.
length

SUBSTR() function in Oracle SQL:

A substring is a sequence of characters within a string. The SUBSTR() function extracts a substring with a specified length for the given index position in an input string.

Syntax of the SUBSTR() function :

SELECT SUBSTR('string', starting_position,substring_length) "column_name"
FROM table_name;
  • Starting_Position: It is the starting position of the character. The index of the string_position starts from 1.
  • String_length: It is the ending position of the character. The string_length is counted from the ending position of the string_position.
  • Space is counted in the starting_position and the string_length.

SUBSTR() in DUAL table:

Example of the SUBSTR() function :

SELECT SUBSTR( 'hello oracle sql', 3,9 ) SUBSTRING
FROM dual;

In this example, the SUBSTR() function returns a substring whose length is 9(including space), starting from the beginning of the third character.
substr_1

SUBSTR() in the created table:

The SUBSTR() can be used in the user-created table. The below query uses the COUNT() and GROUP BY() functions get table_name and their counts based on the initials.

Example of the SUBSTRING() function in the created table :

SELECT SUBSTR( first_name, 1, 2 ) initial_words ,
COUNT (*) FROM persons_table
GROUP BY SUBSTR( first_name, 1, 2 );

retrieved-data

In the below query, the first two characters are counted and grouped.
substr()

REPLACE() function in Oracle SQL:

The Oracle REPLACE() function replaces the sequence of the characters in a string with another set of characters.

Syntax of the REPLACE() function :

SELECT REPLACE(string_expression, string_pattern [,string_replacement])
FROM table_name;
  • string_expression is a string (or an expression that evaluates to a string) to be searched.
  • string_pattern is a substring to be replaced.
  • string_replacement is the replacement string.

Example of the REPLACE() function :

SELECT REPLACE ('Welcome to oracle sql','to','TO')
FROM DUAL;

In this query, the lowercase 'to' is replaced by uppercase 'TO'.
REPLACE-FUNC

REVERSE() function in Oracle SQL:

This function is used to reverse the given string value in the given table.

Syntax of the REVERSE() function :

SELECT REVERSE ('string')
FROM DUAL;

REVERSE() function in the DUAL table:

Example of the REVERSE() function :

SELECT REVERSE ('oracle sql')
FROM DUAL;

The string 'oracle sql' is reversed to 'lqs elcaro' in the output.
reverse-func

REVERSE() function in the created table:

Example of the REVERSE() function in the created table :

SELECT REVERSE (first_name)
FROM Persons_table;

retrieved-data

The strings which are present in the first_name column are reversed.
reverse-func-sql

LOWER() function in Oracle SQL:

This function converts all the letters of the string to the lowercase.

Syntax of the LOWER() function :

SELECT LOWER ('STRING')       
FROM table_name;

LOWER() in the DUAL table:

Example-1 for the LOWER() function :

SELECT LOWER ('DATABASE')
FROM DUAL;

In this query, all the entered string is in the uppercase. And those characters are converted into the lowercase using the LOWER() function.
LOWER_CASE

Example-2 :

SELECT LOWER ('DATaBAsE')
FROM DUAL;

In this query, the characters 'a' and 's' are entered in the lowercase, and the rest of the characters are in uppercase. But in the output string, all the characters are converted into the lowercase using the LOWER() function.
lower_case_1

LOWER() in the created table:

Syntax of LOWER() function in the created table.

SELECT column_name_1, column_name_2,....
FROM table_name
WHERE LOWER(column_name) = 'lower_string';

Existing of LOWER() function in the created table.

SELECT first_name, last_name
FROM persons_table
WHERE LOWER(last_name) = 'patel';

retrieved-data

In the below query, the data is retrieved where the last_name belongs to 'PATEL', 'patel', or 'Patel' by using the LOWER() function.
lowercase

UPPER() function in Oracle SQL:

This function converts all the letters of the string to the uppercase.

Syntax of the UPPER() function :

SELECT UPPER ('string')       
FROM table__name;

UPPER() in the DUAL table:

Example of the UPPER() function :

SELECT UPPER ('oracle')
FROM DUAL;

upper_case
In the above query, all the entered string is in the lowercase. And those characters are converted into the uppercase using the UPPER() function.

Let us understand how the UPPER() function is used in another case.

Example :

SELECT UPPER ('oraCLe')
FROM DUAL;

In this query, the characters 'C' and 'L' are in the uppercase, and the rest of the characters are in lowercase. In the output string, all the characters are converted into the uppercase using the UPPER() function.
UPPER_CASE_1

UPPER() in the created table:

Example of the UPPER() function in the created table.

SELECT first_name, last_name, city
FROM persons_table
WHERE UPPER(city) = 'HASSAN';

retrieved-data

In the below query, the data is retrieved where the city name belongs to 'hassan', 'HASSAN', or 'Hassan' by using the UPPER() function.
uppercase

Conversions functions are used to convert from one data type to another type. The TO_CHAR() function converts a DATE value to the string using the specified format.

Syntax of the TO_CHAR() function :

TO_CHAR(date, ’date_format’)

Example of the TO_CHAR() function :

SELECT TO_CHAR( sysdate, 'YYYY-MM-DD' )
FROM dual;

The below query converts the current system date to a string with the format 'YYYY-MM-DD'.
to-char

INTICAP() function in Oracle SQL:

This function converts the first letter of all the words into the uppercase and the rest of the letters into the lowercase.

Syntax of the INTICAP() function :

SELECT INTICAP ('string_1 string_2.......')
FROM table_name;

INTICAP

() in the DUAL table:

Example of the INTICAP() function :

SELECT INTICAP ('welcOMe to oRACLE')
FROM DUAL;

In the below query, all the starting string is converted to the upper case from the lower case.
INTICAP

INITCAP() in the created table:

Example of the INITCAP() function in the created table :

SELECT INITCAP(last_name)
FROM persons_table;

In the above query, all the details are retrieved from the Persons_table.
retrieved-data-2

In the below query, the first character of all the strings (which are not in the uppercase) is converted using the INITCAP() function.
initcap-sql-func

Character functions in SQL

The character function is a function that accepts the character input value as the parameter and returns the character or numeric values. CHAR or VARCHAR character datatype converts into the numeric datatype like INTEGER or character datatypes like CHAR or VARCHAR.

The commonly used character functions are as follows:

  • INSTR
  • LPAD
  • RPAD
  • TRIM

Let us study the above character functions one by one.

INSTR() function in the SQL:

The INSTR() function finds the substring in a string and also the position of the substring of the given string. This function is used to find the position of the character.

Syntax of the INSTR() function :

INSTR(string , substring [, starting_position [, occurrence]])
  • String : The string which contains the substring.
  • Substring : The substring is needed to be searched.
  • Starting position : The starting position is optional. if this is omitted, then the default value is always 1. The position where the string function needs to be searched. If the position is negative, then the function counts from the backward.
  • Occurrence : This is optional. The occurrence of the substring in the given string.

Example of the INSTR() function :

SELECT INSTR('WELCOME TO ORACLE','O',1,3) 
FROM DUAL; 

In this query, the position of the third 'o' is seen.
instr()-function

Example of the INSTR() function where the starting position and the occurrence are omitted.

SELECT INSTR('WELCOME TO ORACLE','O') 
FROM DUAL; 

In this query, there is no position value and no occurrence value. So, the position of 'o' is searched for first 'o' in the given string.
instr()-func

SELECT INSTR('WELCOME TO ORACLE','O',-1,3) 
FROM DUAL;

In this query, the 'o' is counted from the backward. So, the position is 5.
instr-func-backward

LPAD and RPAD functions in SQL

PAD is nothing but filling the empty character with a character we wish. For example, consider we have a word called "Monday", has 6 letters and if we want to change it to have 10 letters word then we need to fill some dummy letters either Left side or right side. So that result would look like ****Monday or Monday####.

LPAD function is used to add a character in place empty on the Left Side. It is short for LEFT PAD. RPAD function is used to add a character in place empty on the Right Side. It is short for RIGHT PAD.

Syntax of the LPAD/RPAD function :

SELECT column_names, 
LPAD/RPAD(column_name, word_size, fill_character) 
FROM table_name;
  • Column_name : name of the column.
  • Word_size : The required size of the word.
  • Fill_characters : The characters which are required to fill the empty character.

Example-1 of the LPAD function :

SELECT first_name, 
LPAD(first_name, 10, '#'), RPAD(first_name, 10, '@')
FROM Persons_table;

In this query, the LPAD in the first_name gives the '#' until it reaches 10 characters. And RPAD in the firat_name gives the '@' until it reaches 10 characters.
lpad-func

Example-2 :

SELECT first_name, 
LPAD(first_name, 2, '#'), RPAD(first_name, 2, '@')
FROM Persons_table;

If the word_size is lesser than the number of the characters, then the characters will be retrieved according to given the word_size.
lpad-rpad

Check what happens when we use more than one fill character.

TRIM function in SQL

The TRIM function removes a specific character from the given values; if no character is specified then it will remove spaces. The TRIM function removes the characters only from the ends, not something present in between the words.

The syntax of the TRIM function :

SELECT 
TRIM (SIDE 'character' FROM column_name) column_name_for_output 
FROM table_name;

We can remove characters from either ends or both ends:

  • LEADING : Removes the specified character from the left side
  • TRAILING : Removes the specified character from the right side
  • BOTH : Removes the specified character from both sides
  • NO Value : Same as BOTH

Example for the TRIM function in the DUAL table :

SELECT TRIM( '  xyz  ' )
FROM dual;

This query removes the space of both the ends in the DUAL table
trim-dual

Example of the TRIM function in the created table :

SELECT 
TRIM (BOTH FROM first_name) name 
FROM Persons_table;

This query removes the space of both the ends in the created table.
trim-both-func

Example of the LTRIM function in the DUAL table :

SELECT LTRIM('annahitha', 'a') FROM dual;

This query removes the character from the left side in the DUAL table.
ltrim-dual

Example of the LTRIM function in the created table :

# removes the specific character from the LEFT side
SELECT first_name, 
LTRIM( first_name, 'R' ) 
FROM persons_table;

This query removes the character from the left side of the created table.
ltrim()

Example for the TRIM using a LEADING function in the created table :

# removes the spaces from the LEFT side
SELECT 
TRIM (LEADING FROM first_name) name 
FROM Persons_table;

This query removes the space from the left side of the created table.
trim-leading

Example of the RTRIM function in the DUAL table :

SELECT RTRIM('annahitha', 'a') FROM dual;

This query removes the character from the right side in the DUAL table.
rtrim-dual

Example of the RTRIM function in the created table :

SELECT first_name, 
RTRIM( first_name, 'a' ) 
FROM persons_table;

This query removes the character from the right side in the created table.
rtrim()

Example for the TRIM using a TRAILING function in the created table :

# removes the spaces from the right side
SELECT 
TRIM (TRAILING FROM first_name) name 
FROM Persons_table;

This query removes the space from the right side in the created table.
trim-trailing

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions