Table of Contents
SQL specifies strings by enclosing them in single quotes for example ‘SQL tutorial’, SQL permits a variety of functions on character strings, such as concatenating, extracting substrings, finding the length of strings, and many more to perform different operations on strings.
To remove characters from strings in SQL we need to learn some basic SQL string functions which will combine used to remove characters based on various specified conditions.
Different functions to remove characters from string in SQL
- LEN()
- LEFT()
- RIGHT()
- CHARINDEX()
- REPLACE()
- TRIM()
- SUNSTRING()
1. SQL LEN() function
SQL SERVER LEN() function is used to find length of given string , It takes only one parameter that is the string whose length you need to find. in oracle database . LENGTH() is used in oracle database.
Example 1: Write SQL query to find length of string ‘SQL tutorials’
SELECT LEN('SQL tutorials') AS 'LEN() function'
OUTPUT:
2. SQL LEFT() function
SQL LEFT() function is used to extract a specified number of characters from the left-hand side of the given string
Example 2: Write SQL query to extract the first three characters from the left side of the given string ‘SQL string function’
SELECT LEFT('SQL string function', 3) AS 'LEFT() function'
OUTPUT:
3. SQL RIGHT() function
SQL RIGHT() function is used to extract a specified number of characters from the right-hand side of the given string
Example 3: Write SQL query to extract eight characters from the right side of given string ‘SQL string function’
SELECT RIGHT('SQL string function', 8) AS 'RIGHT() function'
OUTPUT:
4. SQL CHARINDEX() function
SQL CHARINDEX() function is used to find the position of a specified character or substring in the given string. If a character or substring is not found, it will return 0
Example 4: Write SQL query to find ‘SQL’ substring in the string ‘SQL string function’
SELECT CHARINDEX('SQL', 'SQL string function', 0) AS 'CHARINDEX() function'
In the above query, ‘SQL string function’ is the string in which the ‘SQL’ substring will search and find the index from the ‘0’ position, starting of string
OUTPUT:
5. SQL REPLACE() function
SQL REPLACE() function is used to replace all occurrences of a specified substring into a given string with newly specified substring,
note that SQL REPLACE() function is case-sensitive
Example 5: Write SQL query to replace ‘string’ substring with ’numeric’ substring’ in the string ‘SQL string functions’
SELECT REPLACE('SQL string functions', 'string', 'numeric') AS 'REPLACE() function'
In the above query, ‘SQL string function’ is the string in which ‘string’ is going to be replaced with ‘numeric’ string
OUTPUT:
6. SQL TRIM() function
The TRIM() function is used to remove the space character OR other specified characters from the start or end of a string.
By default, the TRIM() function removes leading and trailing spaces from a string.
Example 6: Write SQL query to remove leading and trailing spaces from given string ‘ SQL string functions ‘
SELECT TRIM(' SQL string function ' ) AS 'TRIM() function';
OUTPUT:
7. SQL SUBSTRING() function
SQL SUBSTRING() function is used to extract a specified number of characters from a given start position within a given string.
Example 7: Write SQL query to extract SQL from ‘SQL string function’
SELECT SUBSTRING('SQL string function', 1, 3) AS 'SUBSTRING() function'
In the above query, ‘SQL string function’ is the string from the first characters from index 1 is extracted
OUTPUT:
Some Practical Examples
Example-1: SQL remove characters from string right after character
To remove characters right after the specified character we first need to find an index of the specified character using the CHARINDEX() function into a given string and then extract left remaining characters from the string using the LEFT() function
Example 8: Write SQL query to remove all right characters after the character ‘,’ in given string ‘SQL, Functions’
SELECT LEFT('SQL, Function', CHARINDEX(',', 'SQL,Function') - 1) AS 'Remove characters from string right after character '
- In the above query, the SQL CHARINDEX() function is applied with two arguments to find the index of character ’,’ in the given string ‘SQL, Function’
- SQL LEFT() function is used to extract and retrieve the rest of the characters from the left side till the index of ‘,’ character
OUTPUT:
Example-2: SQL remove specific characters from string
To remove specific characters from a given string, we can use the either TRIM() function or REPLACE() function
With SQL TRIM() function we need to specify a set of characters to be removed in single quotation and then the string from which characters should be removed
Example 9: Write SQL query to remove SQL from the string ‘SQLfunctionSQL’
SELECT TRIM('SQL' FROM 'SQLfunctionSQL') as 'SQL remove specific characters from string';
In the above SQL query, SQL TRIM() function is applied to trim specified characters ‘SQL’ from the given string ‘SQLfunctionSQL’
OUTPUT:
With SQL REPLACE() function to remove characters from a given string, first, we need to specify the string from which the characters should be removed and then specify characters to be removed as the second argument, the third argument should the blank as we want to remove characters
Example 10: Write SQL query to remove ‘SQL’ from a given string ‘SQLfunctionSQL’
SELECT REPLACE('SQLfunctionSQL', 'SQL', '') as 'SQL remove specific characters from string';
In the above SQL query, SQL REPLACE() function is applied to replace specified characters ‘SQL’ with blank ‘’ in the given string ‘SQLfunctionSQL’
OUTPUT:
Example-3: SQL remove characters from string left
To remove characters from the string left, first, we need to retrieve the left string from the given string using SQL SUBSTRING () and CHARINDEX() function
After retrieving the left most string to remove characters from the string left we can use SQL LEFT() function with the combination of the SQL LEN() function
Example 11: Write SQL query to remove two leftmost characters from string left ‘SQL’ of given string ‘SQL String Functions ’
SELECT LEFT(SUBSTRING('SQL String Functions', 1, CHARINDEX(' ', 'SQL String Functions')), LEN(SUBSTRING('SQL String Functions', 1, CHARINDEX(' ', 'SQL String Functions'))) - 2) AS 'Remove first 2 characters'
- In the above query, the Innermost SQL SUBSTRING() and CHARINDEX() function is used to find an index of ‘ ‘ space to retrieve left most string from the given string
- SQL LEFT() function is used to remove two characters from the given string left
OUTPUT:
Example-4: SQL remove characters from string right
To remove characters from string right, first, we need to retrieve the right string from the given string using SQL SUBSTRING () and CHARINDEX() function
After retrieving the right-most string to remove characters from the string right we can use SQL RIGHT() function with the combination of the SQL LEN() function
Example 12: Write SQL query to remove two rightmost characters from string right ‘String Functions’ of given string ‘SQL String Functions’
SELECT LEFT('SQL String Functions', LEN(SUBSTRING('SQL String Functions', CHARINDEX(' ', 'SQL String Functions', CHARINDEX(' ', 'SQL String Functions')), LEN('SQL String Functions'))) - 2) AS 'Remove characters from string right'
OUTPUT:
Example-5: SQL remove last character from string
There are two ways to remove the last character from a string in SQL
- We can used combination of SQL SUBSTRING() and SQL LEN() function
- We can use a combination of SQL LEFT()and LEN () function
Example 12: Write SQL query to remove the last character ‘s’ from a given string ‘SQL String Functions'
SELECT SUBSTRING('SQL String Functions', 1, LEN('SQL String Functions') - 1) AS 'SQL remove last character from string '
OUTPUT:
SELECT LEFT('SQL String Functions', LEN('SQL String Functions') - 1) AS 'SQL remove last character from string'
OUTPUT:
Example-6: SQL remove last character from column
Consider student table of school database to remove the last character from the student name column
As mentioned above we can remove the last character of the string using two ways either with SUNSTRING() and LEN() function or with LEFT() and LEN() function
Example 13: Write SQL query to remove the last character of student name column value
SELECT SUBSTRING(studentname, 1, LEN(studentname) - 1) AS 'Remove last character' FROM tblstudent
OUTPUT:
SELECT LEFT(studentname, LEN(studentname) - 1) AS 'Remove last character' FROM tblstudent
OUTPUT:
Example-7: SQL remove last 4 characters from string
There are two ways to remove the last 4 characters from a string in SQL
- We can used combination of SQL SUBSTRING() and SQL LEN() function
- We can use a combination of SQL LEFT()and LEN () function
Example 14: Write SQL query to last four characters from given string ‘SQL String functions'
SELECT SUBSTRING('SQL String Functions', 1, LEN('SQL String Functions') - 4) AS 'Remove last 4 character' FROM tblstudent
OUTPUT:
SELECT LEFT('SQL String Functions', LEN('SQL String Functions') - 4) AS 'Remove last 4 characters'
Example-8: SQL remove characters from end of string
To remove the specified number of characters from the end of the string we can use SQL SUBSTRING () or SQL LEFT() two different functions
Example 15: Write SQL query to remove 3 characters from end of string ‘SQL String Functions’
SELECT LEFT('SQL String Functions', LEN('SQL String Functions') - 3) AS 'Remove last 3 characters'
OUTPUT:
SELECT SUBSTRING('SQL String Functions', 1, LEN('SQL String Functions') - 3) AS 'Remove last 3 characters'
OUTPUT:
Example-9: SQL remove characters from beginning of string
To remove the specified number of characters from the end of the string we can use SQL SUBSTRING () or SQL RIGHT() two different functions
Example 16: Write SQL query to remove the first 2 characters from the ‘SQL String Functions’ string
Summary
In this Article on SQL remove characters from strings, We have covered an overview of removing characters from strings and how to define strings in SQL, We have listed out all seven functions that will be used to remove characters from a given string, Explain each function LEFT(), RIGHT(), LEN(), CHARINDEX(), REPLACE(), TRIM() and SUBSTRING() with practical examples, also covered various practical problem examples like remove all specified characters from a string, remove characters from string left, remove characters from string right, remove characters from the beginning of the string, remove characters from the end of the string, remove last 4 characters of the string.
References