Joining columns causes leading whitespace

#1

Hi all, I am trying to join two columns

select Name, (ColumnB+ColumnC) as Merged

but when I do this I end up with a few spaces before every item in the merged column. I tried

Select Name, trim((ColumnB+ColumnC)) as Merged but then my report doesn't work at all.

Surely I'm just being an idiot here, but could someone help please?

#2

select Name, rtrim(ColumnB) + lrtrim(rtrim(ColumnC)) as Merged

that will leave NO space between them. If you want a single space, add it yourself:

select Name, rtrim(ColumnB) + space(1) + ltrim(rtrim(ColumnC)) as Merged

#3

Thanks Scott actually (and I should have said this in original post, sorry) I want a dash in between so I'd end up with John-123

Actual code is (ColumnA+'-'+ColumnB) as Merged

I must add that there are no leading spaces when I report on these columns individually so not sure why the joining would do that? Quite happy to use your example but didn't want to just blindly do it if there as something else going on. Thanks

#4

I can't see your data, obviously, but there's no special thing going on in SQL generally.

select Name, rtrim(ColumnB) + '-' + rtrim(ColumnC) as Merged

#5

Great stuff, i used LTrim(ColumnA+'-'+ColumnB) and that is working nicely. Thank you for the quick response and the great help, John

#6

Alternative methods:

CONCAT(ltrim(rtrim(ColumnA)), '-', ltrim(rtrim(ColumnB)))
LTRIM(CONCAT(ColumnA, '-', ColumnB))

If either column contains a NULL value - the plus operator will return a NULL value for the expression, whereas CONCAT will return blanks for each.

Example: ColumnA is null and ColumnB is ZZZ

Plus Operation Returns: NULL
CONCAT Returns: -ZZZ