Re: Squeezing spaces out of a string

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.

OK, here's something to get round the problem of the string already containing '>' or '<' characters:

UPDATE Foobar
  SET col_x

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

Basically you're "escaping" the '>' and '<' on the right and left by protecting them with the '\' character. I'm fairly sure this works for all possible combinations of '\', '<' and '>' characters in the initial string.

Doing this does expand the string slightly though, so you do get back to the possibility of overflowing the maximum string size. Maybe there's a way of simplifying the combination of these two operations somehow?

Paul. Received on Tue Feb 17 2004 - 13:25:45 CET