Re: Squeezing spaces out of a string

From: Paul <pbrazier_at_cosmos-uk.co.uk>
Date: 17 Feb 2004 03:49:00 -0800
Message-ID: <51d64140.0402170349.da56bf0_at_posting.google.com>

joe.celko_at_northface.edu (--CELKO--) wrote in message news:<a264e7ea.0402041037.9013551_at_posting.google.com>...
> Assume '<' and '>' do not appear in col_x.
>
> UPDATE Foobar
> SET col_x
> = REPLACE (
> REPLACE (
> REPLACE(col_x, SPACE(1), '<> '),
> '><', SPACE(0)),
> '<>', SPACE(1));
>
> This is due to a guy named Carnegie; Steve Kass found it in an old
> posting. The only problem is that it fails if the the first function
> call overflows the maximum string length. You might get errors, or
> truncation depending on your SQL.

That is very clever, yes. I've spent some time in the past thinking about a similar problem and didn't get such an elegant solution. So easy when you see how! You can get round the string overflow problem though:

UPDATE Foobar

   SET col_x

• REPLACE( REPLACE ( REPLACE ( REPLACE(col_x, SPACE(2), '<>'), '><', SPACE(0)), '<>', SPACE(1)), SPACE(2), SPACE(1))

still the problem of if you have the '<' or '>' chars in the string already though.

Paul. Received on Tue Feb 17 2004 - 12:49:00 CET