Manipulate Strings in SQL Server with REPLACE, TRIM, STUFF, CHARINDEX

Problem

We all know developers who are wizards at crafting scripts to overcome string challenges. On the other hand, some developers focus on the app or presentation layer. Recently, someone asked me to assist in removing characters from a massive list of customer names. For example, a customer’s name might be:

001 GamesRus
05 Joystick 2 Junction
2Game Garage

The number prefix enables users to sort customers in the application grid. By default, the app sorts the names ascending. This isn’t the greatest design, but users find a way to make anything work.

A list of customers needed to go to another group who asked us to remove the digits. Usually, I’d combine CHARINDEX, SUBSTRING, or STUFF and call it a day. But, since someone less experienced with SQL would continue to work on this project, I wanted to make it easy.

Solution

In this article, I’ll share the one function I used to remove those pesky numeric prefixes. We’ll examine how people have used LTRIM and RTRIM for years. Also, there is a little-known function that combines the power of the two into one. With SQL Server 2022, Microsoft added a significant enhancement to the trimming functions. Even though I like the new functionality, there’s one change I want to see. Finally, I hope you apply my examples to solving your string challenges today.

Exploring SQL LTRIM and SQL RTRIM

Microsoft added the LTRIM and RTRIM functions before SQL Server 2000. I found an article from the late ’90s referencing them, so they’re old enough to enjoy an adult beverage. What exactly are they used for? When you need to remove leading or trailing spaces, consider adding one. Several RDMS offer them, including Oracle, MySQL, and PostgreSQL. You can also use these functions in combination with each other.

-- mssqltips.com
-- Here I add two leading spaces.
SELECT LTRIM('  That rug really tied the room together.');
-- Here I add two trailing spaces.
SELECT RTRIM ('Forget it, Donny, you''re out of your element!  ');
-- Here I add two trailing and leading spaces.
SELECT LTRIM(RTRIM('  Hey, Careful, Man, There''s A Beverage Here!  '));

Results:

-----------------------------------------
That rug really tied the room together.
-----------------------------------------------
Forget it, Donny, you're out of your element!
-----------------------------------------------
Hey, Careful, Man, There's A Beverage Here!

You can also use them with other string functions like REPLACE and TRANSLATE. Sometimes, we use them in a WHERE clause to remove empty spaces when comparing strings for equality.

-- mssqltips.com
;WITH Customers
AS (SELECT '  Video Games Rus  ' AS CustomerName
    UNION ALL
    SELECT 'Joystick Junction  '
    UNION ALL
    SELECT '1-Up Arcade')
SELECT LTRIM(RTRIM(CustomerName)) AS CustomerName
FROM Customers
WHERE LTRIM(RTRIM(CustomerName)) = 'Video Games Rus';

Results:

CustomerName
-------------------
Video Games Rus

Ideally, you would remove the empty spaces at the source, but we don’t always have that option. Since we often nest them to remove leading and trailing spaces, let’s look at a function that combines their powers.

Exploring SQL TRIM

Microsoft added the TRIM function in SQL Server 2017. TRIM combines LTRIM and RTRIM into one, so you no longer need to wrap both functions together.

-- mssqltips.com
SELECT TRIM('  Just a Flesh Wound  ');

Since I’ve used LTRIM and RTRIM for so long, it’s easy to forget about TRIM and revert to the older ones. Old habits die hard. Next, we’ll look at an enhancement to all three functions in SQL Server 2022.

SQL Server 2022 Enhancements

With the release of SQL Server 2022, the team at Microsoft extended the capabilities of our beloved trimming functions. You can now pass in an optional character argument indicating what to trim from the string. For example, say you want to remove the first three characters from the string below.

-- mssqltips.com
SELECT LTRIM('Tis but a scratch','Tis');

Results:

-----------------
 but a scratch

Notice how we removed the first three characters from the left of our string. This behavior is unlike the REPLACE function, where SQL matches the exact string. The example above resembles TRANSLATE by removing any reference to the character versus an exact match.

Remember, if you’re using SQL Server 2022, you must set the database compatibility level to 160 for this to work. This new functionality is also built in if you’re on an Azure flavor of SQL.

Removing Customer Order Prefix

As mentioned in the problem section above, my goal was to give the developer an easy, repeatable solution for removing number prefixes from the customer list. I’ve listed below a few facts as to the makeup of our customer list:

None of the customer names started with a number, for example, 1-Up Video Games.
Some names did contain a number, like Joystick 2 Junction.
The business didn’t use letters in the prefix, like aaa Joystick 2 Junction.
The numbers spanned from 0 to 5. However, they were inconsistent, for example, 001, 01, 0004, etc.

Using the abovementioned criteria, let’s examine a few options for resolving this problem and see where some fell short.

SQL REPLACE

Can we use REPLACE to remove the numbers? While the answer is yes, the problem with REPLACE is that it removes any numbers after the prefix if they match the string. Plus, we need to nest several REPLACE functions to capture all number combinations.

-- mssqltips.com
;WITH Customers
AS (SELECT '001 Video Games Rus' AS CustomerName
    UNION ALL
    SELECT '01 Joystick 2 Junction'
    UNION ALL
    SELECT '2 Video Game Tower'
    UNION ALL
    SELECT '0005 Savage Video Games 123')
SELECT REPLACE(REPLACE(REPLACE(REPLACE(CustomerName, '001', ''), '01', ''), '2', ''), '0005', '') AS CustomerName
FROM Customers;

Results:

CustomerName
-------------------------------------
 Video Games Rus
 Joystick  Junction
 Video Game Tower
 Savage Video Games 13

Notice for the second customer: we removed the ‘2’ between Joystick and Junction. Also, the code gets hard to read once you nest about ten levels of REPLACE. Let’s keep on trying.

SQL TRANSLATE

What about TRANSLATE? Doesn’t it remove characters? Yes, it does. But TRANSLATE removes any references to the characters. In the examples where we have a number somewhere in the middle of the name, the function would also remove it.

-- mssqltips.com
;WITH Customers
AS (SELECT '001 Video Games Rus' AS CustomerName
    UNION ALL
    SELECT '01 Joystick 2 Junction'
    UNION ALL
    SELECT '02 Video Game Tower'
    UNION ALL
    SELECT '0005 Savage Video Games 123')
SELECT TRANSLATE(CustomerName, '012345', '      ') AS CustomerName
FROM Customers;

Results:

CustomerName
-------------------------------
    Video Games Rus
   Joystick   Junction
   Video Game Tower
     Savage Video Games

As you can see in the example above, we lost all our numbers. Let’s try one more before using LTRIM.

SQL STUFF and CHARINDEX

Before SQL Server 2022, I would combine STUFF and CHARINDEX or even SUBSTRING and PATINDEX to solve this. However, my primary goal was to make this easy for the developers to understand and copy.

-- mssqltips.com
;WITH Customers
AS (SELECT '001 Video Games Rus' AS CustomerName
    UNION ALL
    SELECT '01 Joystick 2 Junction'
    UNION ALL
    SELECT '02Video Game Tower'
    UNION ALL
    SELECT '0005 Savage Video Games 123')
SELECT STUFF(CustomerName, 1, CHARINDEX(' ', CustomerName), '') AS CustomerName
FROM Customers;

Results:

CustomerName
---------------------------------
Video Games Rus
Joystick 2 Junction
Game Tower
Savage Video Games 123

The one above isn’t bad at all. The code assumes we always have a space between the sort order and the customer’s name. No doubt we could solve this with a PATINDEX and a bit more tinkering, but my prime objective is to keep it simple.

Picking LTRIM

With the enhancements of SQL Server 2022, picking LTRIM was an easy choice for me. Below is the code I used to solve this problem. Notice that in my second argument, I include a space (more on it in the next section).

-- mssqltips.com
;WITH Customers
AS (SELECT '001 Video Games Rus' AS CustomerName
    UNION ALL
    SELECT '01 Joystick 2 Junction'
    UNION ALL
    SELECT '02 Video Game Tower'
    UNION ALL 
    SELECT '0005 Savage Video Games 123')
SELECT LTRIM(CustomerName,'012345 ') AS CustomerName
FROM Customers;

Results:

CustomerName
---------------------------
Video Games Rus
Joystick 2 Junction
Video Game Tower
Savage Video Games 123

Drawback

The problem with LTRIM is that once you provide the second argument, it no longer removes leading spaces. The workaround I found was to add the space as part of the argument to trim, as in the above code. You could also add another LTRIM if you don’t want to include the space.

-- mssqltips.com
SELECT LTRIM(LTRIM(CustomerName,'012345')) AS CustomerName

Adding the additional space is an okay solution, but it seems clunky. Shouldn’t LTRIM do what we initially used it for? Yet, we solved our problem with one function. The development team can quickly reproduce this in the future. Who knows, maybe they will start learning all the string functions T-SQL has to offer.

Conclusion

In this article, I reviewed several solutions to resolve my problem. But in the end, I picked LTRIM. Remember, if you’re not on SQL Server 2022, using LTRIM for this problem is a non-starter. Also, let me clarify by saying there is nothing wrong with using multiple string functions to resolve this. I do it all the time. However, if you’re sharing code with DEVs who don’t live and breathe T-SQL, consider simplifying it.

Key Points

Starting with SQL Server 2022, you can provide an optional second argument indicating what characters to trim from the left or right of a string.
The functionality of LTRIM, RTRIM, and TRIM in SQL Server 2022 behaves like TRANSLATE in that it removes any instance of the character and not a specific string like with the REPLACE function.
If you want to remove the spaces left behind, include the space in the second argument or wrap the functions in another TRIM.

Next Steps

While it didn’t solve the challenge in this article, TRANSLATE was a great addition to SQL Server 2017. I wrote an article comparing it to REPLACE called, ‘REPLACE versus TRANSLATE for SQL Server Text Data Manipulation.’
Check out the article, ‘My Favorite T-SQL Enhancements in SQL Server 2022, for Aaron Bertrand’s favorite T-SQL enhancements with SQL Server 2022.
Microsoft added tons of JSON functionality in SQL Server 2022. Daniel Calbimonte wrote an article exploring two helpful functions titled, ‘JSON_OBJECT and JSON_ARRAY Functions SQL Server 2022 Tutorial.’

Resources

Community

Subscribe