SQL Remove Characters from String with Examples [SOLVED]

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

1. LEN(): Calculate the length of a string, which can be used in combination with other functions like LEFT() or RIGHT() to remove characters.
2. LEFT(): Remove characters from the end of a string by selecting a specified number of characters from the left side of the string.
3. RIGHT(): Remove characters from the beginning of a string by selecting a specified number of characters from the right side of the string.
4. 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).
5. 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.
6. TRIM(): Remove leading and trailing spaces or specified characters from a string.
7. 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.
8. CONCAT(): Concatenate two or more strings together, often used in conjunction with other string manipulation functions like SUBSTRING().
9. STUFF(): Replace a specified length of characters within a string with another set of characters, effectively removing the original characters.
10. 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().
11. 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).
12. 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

SQL String Functions