extra space

[drrocket5292]

I have access to a database that lists every customer account for our company. The first field is the account number, the second field is the last name, the third field is the first name and the fourth field is the middle name. All of these fields for some reason have like 20 spaces after the name as well. I want to concatenate all of these fields so that there is one field that has first name middle name last name, the problem I am having is that some do not have a middle name so when I run a query like this one:

SELECT CUST_NBR AS CIS, Trim([NAME_SEG_2]) & " " & Trim([NAME_SEG_3]) & " " & Trim([NAME_SEG_1]) AS NAME
FROM RMGen
WHERE NAME_LINE_TYPE_CD="P";

I have an extra space between the first name and the last name when there is no middle name. Can someone tell me how to get rid of this extra space? Thanks for the help.

 

[PHV]

You may try this:
SELECT CUST_NBR AS CIS, Replace(Trim([NAME_SEG_2]) & ' ' & Trim([NAME_SEG_3]) & ' ' & Trim([NAME_SEG_1]), ' ', ' ') AS NAME
FROM RMGen
WHERE NAME_LINE_TYPE_CD = 'P'

Or this:
SELECT CUST_NBR AS CIS, Trim([NAME_SEG_2]) & IIf([NAME_SEG_3]>' ', ' ' & Trim([NAME_SEG_3]), '') & ' ' & Trim([NAME_SEG_1]) AS NAME
FROM RMGen
WHERE NAME_LINE_TYPE_CD='P'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[drrocket5292]

Thanks PHV that did the trick.