Monday, September 20, 2010

Eliminating Blank Spaces in SQL Server Tables

Blank spaces always needed special handling in many programming languages.

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:

















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:


--
--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!
[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

12 comments:

Phil Factor said...

Wouldn't it be simpler just to do this?

DECLARE @trimmed VARCHAR(255) ,
@LastTrimmed INT
SELECT @trimmed = 'this has too many spaces' ,
@LastTrimmed = 0

WHILE LEN(@Trimmed) <> @LastTrimmed
SELECT @LastTrimmed = LEN(@Trimmed) ,
@trimmed = REPLACE(@trimmed, ' ', ' ')
SELECT @Trimmed

Artemakis Artemiou [MVP] said...

Hi Phil,

Thank you for your comment.

It is always great to hear from fellow community members.

Your suggestion is very good. Of course you are using a WHILE loop which can be a little bit more expensive from a performance perspective but it is simpler.

Please note that if you try the following it won't work:

Image Link 1

Though, if you try this, yes it will work (add one more space in the REPLACE function parameter):

Image Link 2

And to be more correct and handle any preceding or following blank spaces, you can include to your code LTRIM and RTRIM:

Image Link 3


Cheers,
Artemakis

Phil Factor said...

Somehow, the posting operation took out the second space from the first ' ' in the REPLACE function.(the second ' ' should have one space in it. It may be better to do them as Char(32)+Char(32), Char(32). It was cut/pasted straight from working code, but I couldn't edit it subsequently.

The Ltrim/Rtrim operation would be handy in some circumstances.

Artemakis Artemiou [MVP] said...

Yes, sometimes such things happen :)

Alexandre said...

Hi Artemakis,
the first came to my mind is actually similar to Phil's answer but more a little bit more concise

Solution 1:
while charindex('  ', @text) > 0 set @text = replace(@text, '  ', ' ')

where variable @text contains the string you wont to trim

declare @text varchar(255)
set @text = 'John    F.          Clark'

Note: Use rtrim and ltrim to remove trailing spaces for Solution 1

I agree with you, such a solution might take longer if you have a lot of spaces inside -- in such cases you can use Solution 2.

Solution 2:
declare @index int, @temp varchar(255)
select @temp = '', @index = charindex(' ', @text)
while @index > 0
begin
  set @temp = @temp + substring(@text, 1, @index)
  set @text = ltrim(substring(@text, @index + 1, len(@text) - @index))
  set @index = charindex(' ', @text)
end
set @text = @temp + @text

Artemakis Artemiou [MVP] said...

Hi Alexandre,

Thank you for your comment.

Yes, that's very correct. Your suggestion works for many (if not all) scenarios.

As we see there are many solutions to this issue, the important thing is to first analyze each case and decide which one of the available solutions should be used.

Cheers,
Artemakis

Phil Factor said...

The simple version I've just added is safe for blocks of spaces up to 208 characters long, which should suffice. The huge advantage is its speed, as it requires no UDF to clean up text with space in it. uoi can addt LTRIM and RTRIM to taste

Artemakis Artemiou [MVP] said...

Hi Phil,

Unfortunately I cannot see the newly added code on the blog, I just received it via an email notification. Can you please repost it?

Thanks.

Phil Factor said...

SELECT
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(
'this has too many spaces' ,
REPLICATE(CHAR(32), 6),CHAR(32)),
REPLICATE(CHAR(32), 5), CHAR(32)),
REPLICATE(CHAR(32), 4), CHAR(32)),
REPLICATE(CHAR(32), 3), CHAR(32)),
REPLICATE(CHAR(32), 2), CHAR(32))

/* This version is safe for blocks of spaces up to 208 characters long, (38 if you miss out the replacement for six consecutive spaces, and 10 if you miss out both the five and six consecutive spaces) which should suffice.
The huge advantage is its speed, as it requires no UDF to clean up text with space in it.

Artemakis Artemiou [MVP] said...

Hi Phil,

I agree, this can be a solution too, and a fast one!

As I said in my previous comment, finally it depends on the case when it comes to which solution to use.

The good thing is that there are many solutions for different scenarios :)

Cheers,
Artemakis

priyanka said...

how to find substring in a string
e.g raw material
in this string i want to extract First letter of raw n first letter of material....

Artemakis Artemiou [MVP] said...

Hi priyanka,

Check out my latest article (added today) - Requirement 3.


Cheers,
Artemakis