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:
The above two approaches do not fully solve the problem of the specific blank spaces scenario.
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:
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:
Now let's take a look at the data:
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:
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:
--
--Function Name: NoBlankSpaces
--Purpose: Eliminate blank spaces (up to three words)
--Author: Artemakis Artemiou
--Input Parameter: Varchar(250)
--Output: Varchar(250)
--
CREATE FUNCTION [dbo].[NoBlankSpaces]
(
@string varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
declare @part1 as varchar(100)
declare @part2 as varchar(100)
declare @part3 as varchar(100)
declare @part4 as varchar(100)
declare @temp as varchar(100)
set @part1=(rtrim(substring(@string,0,(charindex(' ',ltrim(@string))))))
set @temp=ltrim(substring(@string,(charindex(' ',@string)),len(@string)+1))
set @part2=SUBSTRING(@temp,0,CHARINDEX(' ',@temp))
set @temp=SUBSTRING(@temp,0,250)
set @part3=ltrim(SUBSTRING(@temp,CHARINDEX(' ',@temp),250))
set @string=rtrim(ltrim(@part1+' '+@part2+' '+@part3))
RETURN @string
END
GO
-------------End of Function DDL-----------------
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:
Cool! No more blank spaces! :)
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!
Cheers!
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!
Cheers!