This is not an exception in the case of the SQL programming language.
In SQL Server, a variety of built-in string functions exist for assisting, among other, the developer to solve data-related problems in the case of undesired blank spaces.
However, the blank spaces scenarios vary and sometimes it is necessary to use a combination of various built-in string functions for solving the problem.
For example, consider having one or more columns within a record as the following:
An approach could be the following, using just the REPLACE built-in function like this:
Also, you could try this:
As you can see, it started getting a little bit tricky regarding how it is possible to efficiently handle the specific blank spaces problem.
Further below I am suggesting a workaround on how to resolve such issues.
To this end, first we need to create a sample data set with a column containing blank spaces:
OK, it's time to implement the actual logic for addressing the blank spaces problem.
As it will be too complex to include the logic within a single T-SQL statement, I preferred to create a function because it will be much easier to use it again in the future and the code looks more structured.
Here's the DDL for my function:
As you can see in the above DDL code, I am using a combination of the SUBSTRING, LTRIM, RTRIM and the CHARINDEX SQL Server built-in string functions for achieving my goal.
Now, let's use the function and see what the outcome will be:
The combination of the SQL Server built-in functions worked great towards the solution of the blank spaces issue.
You can download the code I used for this example from here.
I hope you found this post useful.
Drop me a line if you have any questions or comments!