I have an issue I'm struggling with to remove only trailing spaces from two strings. I have two columns which may or may not contain data. Where there is data in either column, it may or may not have trailing spaces, leading spaces or both. What I want to do is to remove only the trailing spaces (keeping the leading spaces) and concatenate the two columns. So for example, my source data might look like this:
COLUMN1 COLUMN2
'First Bank ' ' Ltd '
'Second Bank '
'Third Bank' 'Ltd '
So I just need to remove the trailing spaces. If I remove them all, the result of the concatenation on the first row would be
'First BankLtd'
Whereas it should be
'First Bank Ltd' (keeping the leading space in column2)
I thought about removing all leading and trailing, then adding a space back in between the concatenated values, but if there's no value in column2 that essentially leaves me with a trailing space on column1!
So I thought I'd come up with a solution with this:
row2.Column2==null ?
row2.Column1 :
row2.Column1+" "+row2.Column2
So I think I'm saying that if column2 is null then use the value in column1, else concatenate column1 & column2, separated by a space.
In the advanced settings in the source file, I also checked column1 & column2 to trim.
But it seems to be ignoring the first condition row2.Column2==null ?
So where I've got null values in column2 it's using the value from column1, but I've ended up with a trailing space as if it's concatenated column1 & 2 with a space in between.
I'm wondering if it's not actually null values in column2, but when I look at the delimited source file, there's definitely no spaces or anything else in that column.
