CONCATENATION PROBLEM

[MelissaKT]

I have a question that may seem kinda stupid but I have to admit that I've worked on this for several hours and can't seem to fix it.

I have a list box that includes the physical address of a property. The physical address is made up of several fields so I concatenated the fields to make it easier to view. It looks really good EXCEPT that there are too many spaces between the different field parts - for example:
PhysicalAddress
1421 MIDDLETON RD TANEYVILLE


I have this as the sql statement:

SELECT     tblPropertyInfo.PropertyNumber, tblOwners.NameOnDeed, tblPropertyInfo.TaxCode, tblPropertyInfo.DBA, tblPropertyInfo.SubdivisionID, 
                      CASE WHEN [Situs_Address_No] IS NOT NULL THEN [Situs_Address_No] + ' ' ELSE NULL END + CASE WHEN [Situs_Direction_Prefix] IS NOT NULL 
                      THEN [Situs_Direction_Prefix] + ' ' ELSE NULL END + CASE WHEN [Situs_Street_Name] IS NOT NULL THEN [Situs_Street_Name] + ' ' ELSE NULL 
                      END + CASE WHEN [Situs_STreet_Type] IS NOT NULL THEN [Situs_Street_Type] + ' ' ELSE NULL 
                      END + CASE WHEN [Situs_Direction_Suffix] IS NOT NULL THEN [Situs_Direction_Suffix] + ' ' ELSE NULL 
                      END + CASE WHEN [Situs_Bldg_Suite_No] IS NOT NULL THEN [Situs_Bldg_Suite_No] + ' ' ELSE NULL 
                      END + CASE WHEN [Situs_Unit_Lot_No] IS NOT NULL THEN [Situs_Unit_Lot_No] + ' ' ELSE NULL END + CASE WHEN [SitusCity] IS NOT NULL 
                      THEN [SitusCity] ELSE NULL END AS PhysicalAddress
FROM         tblPropertyInfo INNER JOIN
                      tblTaxCode ON tblPropertyInfo.TaxCode = tblTaxCode.TaxCode INNER JOIN
                      tblOwners ON tblPropertyInfo.CurrentOwnerID = tblOwners.OwnerID
ORDER BY tblPropertyInfo.TaxCode, tblPropertyInfo.PropertyNumber

I've tried several different ways to trim it (ltrim(rtrim([field name])) + ' '). I'm not sure what I'm doing wrong? Please help!!!

 

[belovedcej]

I'm assuming this is a view or stored procedure rather than sql imbedded into your front-end code.

On that assumption, you could try building a function that could be used to "crunch" the data together (make it a function so that it can be used in other places - this really comes in handy when putting together names, addresses, etc.)

Create a function that takes in a varchar variable (lets call it @input) and returns a varchar. This is off-the cuff - the actual function is at work and I'm at home, but it would be something like this:

Declare @output As varchar(500)
Set @output = Replace(@input, ' ', ' ')
Return @output

The replace function is the key - it looks at the given string, finds anyplace with 2 spaces and replaces it with 1 space.

You may want to modify it a bit, but that should get you started.

 

[MelissaKT]

Sorry for taking so long to answer...I had to go about this a little bit different because this stored procedure is for a list box. I wasn't if or how you could execute a stored procedure from a stored procedure? So, I just put the replace function within the current stored procedure that I'm using. It still has too many spaces in between. Did you see anything wrong with my concatenation? It seems like it is still keeping the "space" for the field that would be there.

I really appreciate your help!

 

[payback]

Will this achieve what you want? It is a Select query for a SQL database from an ADP.

SELECT     TOP 100 PERCENT ID, Description, ISNULL(Address1, N'') + ISNULL(N', ' + Address2, N'') + ISNULL(N', ' + City, N'') + ISNULL(N', ' + State, N'') 
                      + ISNULL(N' ' + ZipCode, N'') AS Address
FROM         dbo.tblNames
ORDER BY Description

 

[MelissaKT]

Hey, thanks for responding. I'm still fighting with it. I try to work on it once a day - that's all my temper will let me try! Anyway, I tried the isnull statements. They aren't working. I think I'm going to have to trim the values or use a len function (i've tried this but...well, see 2nd sentence!). It seems like it "sees" that there is a value there but there isn't. I'd love to have any ideas? Thanks again!

 

[belovedcej]

Try this -

Replace
(LTrim([Situs_Address_No] + ' ' + [Situs_Direction_Prefix] + ' ' +
[Situs_Street_Name] + ' ' [Situs_STreet_Type] + ' ' + 
[Situs_Street_Type] + ' '+ [Situs_Direction_Suffix] + ' ' + 
[Situs_Bldg_Suite_No]+ ' ' + [Situs_Bldg_Suite_No] + ' ' +
[Situs_Unit_Lot_No] + ' ' +  [SitusCity]), '  ', ' ') As PhysicalAddress

It's what I meant by using a function - but it would have been reusable. Perhaps, though, this will work find for your purposes. Essentially, you are replaceing all double spaces with a single space.

 

[MelissaKT]

Thanks for responding again. I know I must seem silly but it's still not doing what it's suppose to do. I actually had used the replace function at one time(right after I got your answer) but it's a good possibility that I used it incorrectly! Anyway, I copied and pasted your code into my db. This is what I got:
1938 CANEY CREEK RD AVA

These fields are varchar fields that do allow null values. I truly appreciate your help!

 

[belovedcej]

Ah! I have never allowed null values - alway zero length strings.

How about changing your case statement, so that if the values are null, they are ''?

 

[danvlas]

LTrim will only remove the spaces on the left side.
In your expression, the string is first built, them the left spaces (none most of the time) are removed.

RTrim will remove the spaces on the right.

You need to remove the spaces from all expression elements before concatenating.

LTrim(RTrim([Situs_Address_No])) + ' ' + LTrim(RTrim([Situs_Direction_Prefix]))...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html