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?
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
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
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
Great stuff, i used LTrim(ColumnA+'-'+ColumnB) and that is working nicely. Thank you for the quick response and the great help, John
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