String Full Name 1

[MeanJoeGreen13]

I have the following that does not seem to work well. It leaves spaces where there is a null value, any idea on what the problem may be?

Set full_name = rtrim(ISNULL(prefx,''))+'' +rtrim(ISNULL(first_name,''))+' ' +rtrim(ISNULL(preferred,''))+' ' +rtrim(ISNULL(middle_name,''))+' ' +
rtrim(ISNULL(last_name,''))+' ' +rtrim(ISNULL(sfx_name,''))

 

[djj55]

Try

Set full_name = rtrim(ISNULL(prefx,'')+''[COLOR=red])[/color] +rtrim(ISNULL(first_name,'')+' '[COLOR=red])[/color] +rtrim(ISNULL(preferred,''))+' ' +rtrim(ISNULL(middle_name,'')+' '[COLOR=red])[/color] +
rtrim(ISNULL(last_name,'')+' '[COLOR=red])[/color] +rtrim(ISNULL(sfx_name,''))

Noticed the moved left parenthesis.
djj

 

[mrdenny]

There is no problem. It's doing exactly what you have told it to do.

You will probably need to put the spaces between the names inside case statements so that the space is put in only when there is something directly infront of the space.

(This isn't tested)

Set full_name = rtrim(ISNULL(prefx,''))+ case when prefx is not null then ' ' else '' end +rtrim(ISNULL(first_name,''))+ case when first_name is not null then ' ' else '' end +rtrim(ISNULL(preferred,''))+ case when preferred is not null then ' ' else '' end  +rtrim(ISNULL(middle_name,''))+ case when middle_name is not null then ' ' else '' end +
rtrim(ISNULL(last_name,''))+ case when last_name is not null then ' ' else '' end +rtrim(ISNULL(sfx_name,''))

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mrdenny]

I thought about djj's solution when I was typing up mine. I didn't go that route as the rtrim should remove the space that you are adding on (I haven't tested so I'm not 100%).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[djj55]

Denny, Good catch.
You would not want to remove the extra space if there is data. What I usually do is something like:

CASE WHEN RTRIM(prefx) = '' THEN '' ELSE RTRIM(prefx) + ' ' END

Good luck MeanJoeGreen13

djj