In today's data-driven world, effective data manipulation is crucial for transforming raw data into meaningful and useful information. One common challenge faced by data professionals is cleaning up and transforming text data. This article will focus on how to remove characters from strings in SQL, a powerful and widely-used database programming language.
We will explore various techniques and built-in SQL functions that can help you efficiently eliminate specific characters or patterns from strings, allowing you to clean up and reformat data to meet your requirements. Whether you're a database administrator, data analyst, or simply someone looking to hone their SQL skills, this comprehensive guide will provide you with the knowledge and practical examples to remove unwanted characters from strings in SQL. By the end of this article, you will be well-equipped to handle text manipulation tasks with ease, ensuring that your data is clean, consistent, and ready for further analysis or reporting.
Different functions to remove characters from string in SQL
Here are a list of possible methods which can be used to remove chars from string in SQL
- LEN(): Calculate the length of a string, which can be used in combination with other functions like LEFT() or RIGHT() to remove characters.
- LEFT(): Remove characters from the end of a string by selecting a specified number of characters from the left side of the string.
- RIGHT(): Remove characters from the beginning of a string by selecting a specified number of characters from the right side of the string.
- CHARINDEX(): Find the position of a specific character within a string, which can be used with other string functions like SUBSTRING(), LEFT(), or RIGHT() to remove the character(s).
- REPLACE(): Replace all occurrences of a specified character or substring with another character or substring within a given string, effectively removing the original character(s) if the replacement is an empty string.
- TRIM(): Remove leading and trailing spaces or specified characters from a string.
- SUBSTRING(): Extract portions of a string, which can be combined with other string functions like CONCAT() to create a new string without the unwanted characters.
- CONCAT(): Concatenate two or more strings together, often used in conjunction with other string manipulation functions like SUBSTRING().
- STUFF(): Replace a specified length of characters within a string with another set of characters, effectively removing the original characters.
- PATINDEX(): Find the position of a specific pattern within a string and use that position to remove the character(s) using other string functions like SUBSTRING(), LEFT(), or RIGHT().
- TRANSLATE(): Substitute multiple single characters in a string with other single characters or remove them if the replacement character is not specified (available in some SQL dialects such as PostgreSQL and SQL Server).
- REGEXP_REPLACE(): Use regular expressions to match and replace complex patterns within a string, providing advanced removal capabilities (available in some SQL dialects such as PostgreSQL, Oracle, and MySQL 8.0+).
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.
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
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
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
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
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.
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.
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
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
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
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
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
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
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
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
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
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
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