Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL: Remove whitespace from in between text

Posted on 2014-09-29
21
Medium Priority
?
256 Views
Last Modified: 2014-09-30
I need to convert

"Hello         how   are       you                                               doing     "

to

"Hello how are you doing"

I tried the following, but that just removes ALL whitespace. I still need the sentence to look normal to include the regular single space between words:

 SELECT REPLACE('Hello         how   are       you                                               doing     ' ,CHAR(32),'')
0
Comment
Question by:pzozulka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +3
21 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 100 total points
ID: 40350456
0
 
LVL 25

Expert Comment

by:jogos
ID: 40350483
If a lot of spaces a lot of recursions can occur as a rule then you can limit it by replacing multiple spaces at each recursion
CREATE FUNCTION dbo.RemoveMultipleSpaces (@str NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF CHARINDEX('  ', @str) = 0
        RETURN @str;
    RETURN dbo.RemoveMultipleSpaces( REPLACE( REPLACE(@str, '       ', ' '), '  ', ' ') );
END
GO

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1268 total points
ID: 40350523
Someone discovered another method that is faster: embedded replaces of power(2) + 1 spaces with a single space.


SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(string)),
        SPACE(33), SPACE(1)), SPACE(17), SPACE(1)), SPACE(9), SPACE(1)), SPACE(5), SPACE(1)),
        SPACE(3), SPACE(1)), SPACE(2), SPACE(1)), SPACE(2), SPACE(1))
FROM (
    SELECT 'Hello         how   are       you                                               doing     ' AS string
) AS test_data
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:pzozulka
ID: 40350768
Does anyone see a problem with the below?
declare @STRING  varchar(100) 
set @STRING =  'Hello         how   are       you                                               doing     '  
set @STRING = REPLACE(REPLACE(REPLACE(@STRING
    ,CHAR(32),'øù')
    ,'ùø','')
    ,'øù',CHAR(32))

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1268 total points
ID: 40350778
The correct version of that method/technique would work, it's just significantly slower than the method I posted earlier.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40350837
The mothod you posted seems to also implicitly performing ltrim and rtrim, is that correct?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40350841
It's explicitly performing an LTRIM and RTRIM.  You can remove that if you want to.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 632 total points
ID: 40351579
The method proposed by yourself at ID: 40350768 assumes that the replacement string pairs used by the replace function do not occur in the source string. If that is not true then the result will be inaccurate.

øù          spaces replaced by this to form:   øùøùøùøùøùøùøùøù
ùø          middle portion ùøùøùøùøùøùøùø then replaced, resulting in:   øù
øù          then replaced by 1 space

The replacements occur by character pairs, the method suggested by Scott works in larger units and does not rely on characters that might occur in the string (except the unwanted spaces).
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40351614
Just replace two spaces ('  ') for nothing (''):
SELECT LTRIM(RTRIM(REPLACE('Hello         how   are       you                                               doing     ' ,'  ','')))

Open in new window

0
 
LVL 1

Expert Comment

by:Sameer Ahamed Sathar
ID: 40351668
Vitor,

In this scenario your solution get failed

SELECT LTRIM(RTRIM(REPLACE('Hello         how   are       you                                                                        doing     ' ,'  ',''))) from dual;

Open in new window


Pzozulka,

You can use simple regexp_replace function to achieve your requirement.If you feel that you will get spaces before "Hello" also,you can add a TRIM before the same.

DECLARE
  v_txt VARCHAR2(1000);
BEGIN
  SELECT TRIM(regexp_replace('          Hello    how      are         you                doing                         ','[[:space:]]+',chr(32)))
  INTO v_txt
  FROM dual;
  
  dbms_output.put_line(v_txt);
END;

Open in new window


- SAM
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40351674
@Sameer seems are you  suggesting Oracle syntax (but the question specifies MS SQL Server in topics)
0
 
LVL 1

Expert Comment

by:Sameer Ahamed Sathar
ID: 40351684
Hi PortletPaul,

It seems the question is for oracle only.As all the comments given was related to that.Need author's reply regarding this.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40351710
@Sameer, what for you added "FROM dual"? I tested it before posting and worked perfectly.
This question is in MS SQL Server area and only have tags to SQL Server. And the code it's pretty much T-SQL.
But of course, the author can clarify this.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40351743
There has been no mention of Oracle, or Oracle syntax, until the first post by Sameer. I see no reason at all to assume this is for Oracle, quite the reverse, IF TSQL was equipped with regexp_replace we would not be wasting our time arguing over how best to use replace.

and nor would the asker have chosen  BOTH MS SQL Server  & SQL Server 2008
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40352303
This is for TSQL.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40352322
Thanks all for your input. I implemented Scott's suggestion, and it seems to not perform any better or worse than the solution I posted. The example I originally gave was just that -- an example.

In reality, all I need it for is to get rid of 1 to 3 whitespaces in between text. So it is highly unlikely that I will ever need to remove powers of 2 + 1 whitespaces.

Having said that, would my posted solution work better for smaller whitespace removals? Since it uses less REPLACE functions?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40352363
You seem to strongly prefer it, so use it.  Until/unless you have large amounts of data, you won't notice any performance hit anyway.  The odds of a stray ø or ù are probably low enough to ignore.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40352372
@pzozulka, my solution didn't fits in what you wanted?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40352403
No, Vitor, it doesn't.  Here's your code against a different input string:

SELECT LTRIM(RTRIM(REPLACE('Hello  how  are  you  doing   ' ,'  ','')))
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40352415
I see. Works for his example but you just gave me a case that will fail.
Thanks Scott. Will try to work on it.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40352425
Your code requires that the extra spaces must appear only in odd numbers: 3, 5, 7, etc..  Needless to say, data is not likely to cooperate with that requirement :-) .
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question