Concatenating Fields Using Double Bars 1

[dash11]

Greetings! I'm working with a CASE statement to concatenate two different address fields together. The field "Alternate Line 1" contains the customers address and "Alternate Line 2" contains the apartment number to the address that's contained in "Alternate Line 1". However, when I run the below code, the results places a huge space between the address and the apartment number, and I'm only wanting there to be just one space between them.

CASE WHEN SUBSTR(Customer_Address.Alternate_Line_1,1,1) IN('1','2','3','4','5','6','7','8','9') THEN (Customer_Address.Alternate_Line_1 ||''|| Customer_Address.Alternate_Line_2)

I'm wondering if it might be due to a conversion error that's taking place when the query is being run. Aside from that guess, I'm pretty much at a loss as to where I'm going wrong.

Thank you kindly!

 

[SkipVought]

Hi,

Try Trim. It’s been a long time since I’ve coded DB2 SQL, so you might be limited to LTrim or RTrim...

(Trim(Customer_Address.Alternate_Line_1) ||''|| Trim(Customer_Address.Alternate_Line_2))

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dash11]

That seemed to do the trick, THANK YOU!

 

[SkipVought]

Just a dash of a Tip.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]