Re: Removing multiple blanks in a string

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 20:07:45 +0200

And if you can't use the method Anith suggested, then you can use this:

update #t
set col1 =
       Replace(
       Replace(
       Replace(
       Replace(
       Replace(
       Replace(col1, replicate('X',132),'X')
                   , replicate('X', 21),'X')
                   , replicate('X', 6),'X')
                   , replicate('X', 3),'X')
                   , replicate('X', 2),'X')
                   , replicate('X', 2),'X')

In this example, multiple X are replaced with a single X, and can handle
varchar(8000) (and bigger).

Hope this helps,
Gert-Jan

Jonathan Blitz wrote:
>
> We need to prevent a user entring a string with multiple blanks between
> words.
> If the user does enter more than one blank we want to reduce it to one.
>
> I tried using the replace function in an "Instead of" trigger but that only
> works but that will only remove one pair at a time so if the user enter an
> odd number of blanks or more than 2 blanks it cannot remove them.
>
> Any ideas?
>
> --
> Jonathan Blitz
> AnyKey Limited
> Israel
>
> "When things seem bad
> Don't worry and shout
> Just count up the times
> Things have worked themselves out." 

-- 
(Please reply only to the newsgroup)

Relevant Pages

  • Re: Moving array formulas
    ... it's not an array formula) ... but to terminate at a multiple of 6 rows ... Note that the formula will return blanks: ...
    (microsoft.public.excel.misc)
  • Re: Mail Rule TO: myuself only
    ... of several people in the TO line (multiple names). ... However, if you are on any mailing lists that you want to receive, they ... multiple names, partial matches, or blanks? ...
    (microsoft.public.windows.inetexplorer.ie6_outlookexpress)
  • Re: lookup and return multiple values
    ... Copy across until you get blanks. ... I am trying to lookup a value in a row and return multiple values from ... I am using the following function to find multiple values in row2 based on ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Mail Rule TO: myuself only
    ... "Belfry" wrote in message ... multiple names, partial matches, or blanks? ...
    (microsoft.public.windows.inetexplorer.ie6_outlookexpress)
Loading