How to concate fields without using CONCAT()

Question

For some reason, I cannot combine the firstname and lastname from a sql table.

Here is what I have tried:

SELECT firstname, lastname, firstname + ' ' + lastname as fullname from students;

For some reason it is showing in the result "0" for each row under fullname, however the firstname and lastname columns are populated as normal.

Please help. ***Please note, I do not want to use CONCAT() function

Screenshot here: table

Thanks in advance

*"they are using concat in the duplicate"* and they are also mentioning `PIPES_AS_CONCAT` SQL mode in MySQL which allows the ANSI SQL string concatenation (`||`) operator @Cid which jarth also mentioned — Raymond Nijland, Apr 16 '19 at 14:20
Some answers need to be edited to add details on how to use `PIPES_AS_CONCAT`. There is links, but links may die. There is comment, but some don't read them — Cid, Apr 16 '19 at 14:23
*"Some answers need to be edited to add details on how to use PIPES_AS_CONCAT"* Also some old "unsafe" answers or tutorials should also be removed from the internet @Cid .. You can't avoid these things.. — Raymond Nijland, Apr 16 '19 at 14:25
*"good luck removing W3School"* Why whats wrong with it....?? @Cib i think if i turn around and ask what is correct about it you done faster :-) — Raymond Nijland, Apr 16 '19 at 14:30
I found another duplicate question that had an answer showing use of `PIPES_AS_CONCAT`. — Bill Karwin, Apr 16 '19 at 15:11

Gordon Linoff · Answer 1 · 2019-04-16T14:35:09.090

6

You are trying to add two strings. That doesn't make sense. Although some (well, really one, given that SQL Server is based on Sybase) database overloads + to mean string concatenation, the proper way to do it is either:

concat(firstname, ' ', lastname) as fullname

or as I prefer because it handles NULL values cleanly:

concat_ws(' ', firstname, lastname) as fullname

or in a database that supports ISO/ANSI operators:

(firstname || ' ' || lastname ) as fullname

This doesn't work in MySQL, unless you have set the mode PIPES_AS_CONCAT (see here).

In MySQL, you will get 0, because + is treated as addition and the two strings are (silently) converted to numbers, based on leading digits. Because both start with non-digits, they are both converted to 0.

edited Apr 16 '19 at 14:35

answered Apr 16 '19 at 13:57

Gordon Linoff

1,242,037
58
646
786

*"well, really one"* by curiosity, which one? – Cid Apr 16 '19 at 13:58
@Cid, could be Sybase. – jarlh Apr 16 '19 at 13:58
1

@Cid I think [SQL Server uses + for concatenation](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1818d48c4945f92a221c715ddfc1a480). – Tim Biegeleisen Apr 16 '19 at 13:59
@TimBiegeleisen thank you – Cid Apr 16 '19 at 14:00
yes @RaymondNijland – Bill Apr 16 '19 at 14:11
still seeing 0 in the result.... – Bill Apr 16 '19 at 14:12
And i do not want to use concat – Bill Apr 16 '19 at 14:12
@Bill . . . If you don't want to use `concat()`, switch to a database that uses your operator of preference. MySQL concatenates strings with `concat()`. – Gordon Linoff Apr 16 '19 at 14:38
IIRC, Microsoft Access also uses `+` for string concatenation. – Bill Karwin Apr 16 '19 at 15:08

score 3 · Answer 2 · answered Apr 16 '19 at 13:57

Just a guess, but maybe your SQL version uses || for the concatenation operator:

SELECT
    firstname,
    lastname,
    firstname || ' ' || lastname AS fullname
FROM students;

The reason you are seeing zero could perhaps be due to that the database is casting the string columns to numbers, to make the + operator make sense.

How to concate fields without using CONCAT()

2 Answers2