0

I usually join the contents in two columns by column1 + column2 in SQL.

However, I saw one code which combines two columns by ||. I ran the following code but basically get the exactly same result.

Is there any difference between using + and ||?

select CurrentDepartment ||'@' || NameId as Full_ID1, CurrentDepartment+'@'+NameId as Full_ID2
from dmf_dbo.personinfo
codingsnake99
  • 146
  • 1
  • 10
  • 1
    `||` is ANSI SQL concatenation. Some products also accept `+`. – jarlh Aug 20 '20 at 08:35
  • 1
    @jarlh, conversely, not all products accept `||` – HoneyBadger Aug 20 '20 at 08:36
  • 2
    Does this answer your question? [String concatenation operator in Oracle, Postgres and SQL Server](https://stackoverflow.com/questions/1373238/string-concatenation-operator-in-oracle-postgres-and-sql-server) - not really a duplicate but the answer fits quite well, I think – Stephan Bauer Aug 20 '20 at 08:37
  • In ANSI standard SQL, `||` is used to concatenate strings (varchar) values and `+` to add numbers. –  Aug 20 '20 at 08:38
  • @a_horse_with_no_name Do you know why the ISO SQL language design committee decided to use that operator specifically? It seems odd to me that so many modern (post-SQL-92) parts of ISO SQL syntax seem different to almost every other programming language out there for no benefit. – Dai Aug 20 '20 at 08:41
  • Maybe because operator overloading wasn't really a thing back in the 80s (or was considered to complicated to implement) so they had to come up with different operators for different data types. But this is really just a wild guess –  Aug 20 '20 at 08:43
  • 1
    `+` is a mathematical operator and makes no sense for character data. How much is green plus Paris? – jarlh Aug 20 '20 at 08:46
  • 3
    @Dai In some old IBM languages (like CL on AS400) `||` means _concatenate_, `|<` means _rtrim left operand and concatenate_ and `|>` _rtrim left operand and concatenate with a blank character in between_ maybe related – nfgl Aug 20 '20 at 08:52
  • @StephanBauer Thanks for the link. That's really helpful. And thanks all for the response. It's clear for me now. – codingsnake99 Aug 20 '20 at 11:49
  • 1
    @a_horse_with_no_name . . . Operator overloading worked just fine in the 1980s and 1990s. SQL is a language that is not supposed to confuse people, and `3 + '0'` is totally ambiguous. Should the answer be `3` or `'30'`. Best never to have to deal with that. – Gordon Linoff Aug 20 '20 at 12:04
  • @GordonLinoff: good to know. It certainly wasn't available with BASIC, COBOL, Pascal and C back then - which is what I was programming with in the 80's ;) –  Aug 20 '20 at 12:10

0 Answers0