removing spaces on a part of the string 1

[brukeste]

Hey World,

I am using version CR XI R2 11.5.8.826.
SQL Server 2005 (migrating to 2008)

I have a string that I need to format by removing specific spaces. The string can be no more than 9 characters long. The space to remove is the FIRST space found starting from the right side of string.

Example:

NEUR S 800 becomes NEUR S800
MEDRCK 691 becomes MEDRCK691
OB GYN 200 becomes OB GYN200
PBSCI 670 does not change

Tried:
Replace(inputString,findString,replaceString,startPosition) but it only returns my starting point.

any suggestions?
Happy New Year to everyone!

Bruce Steinberg
University of Washington
Seattle

 

[lbass]

stringvar array x := split({table.field}," ");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
if i < j-1 or
j <= 2 then (
y := y + x+" "
) else
y := y + x
);
y

-LB