Concatenating blank strings

[seekwall]

I need to be able to derive a single name field from 4 other fields. If I do the following as an example:

declare @title varchar(20)
select @title = 'Mr'
declare @inits varchar(20)
select @inits = 'J'
declare @forename varchar(20)
select @forename = ' '
declare @surname varchar(20)
select @surname = 'Doe'
print @title+' '+@inits+' '+@forename+' '+@surname

I get "Mr J Doe" returned, which is correct.

What I need to do is to be able to return only one space bewteen each populated string no matter where there are blank strings.

Anyone got any ideas ?

 

[tlbroadbent]

Use the REPLACE and RTRIM functions.

print Replace(rtrim(@title)+' '+rtrim(@inits)+' '+rtrim(@forename)+' '+rtrim(@surname),' ',' ') Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin