SQL Remove Characters from String with Examples [SOLVED]

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

1. LEN()
2. LEFT()
3. RIGHT()
4. CHARINDEX()
5. REPLACE()
6. TRIM()
7. 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

SQL String Functions