Big query - Concatenate strings horizontally

Question

I have a data with a column for the first name and a column for the last name. I try to combine the them into one column via code:

SELECT GROUP_CONCAT_UNQUOTED(full_name,' ') 
from (Select first_name as check from [DATA]), 
     (select last_name  as check from [DATA])

But it returns a one row string with

Anna Alex Emma Sean .... Miller Smith White ...

but what I wanted was actually a column like

Anna Miller
Alex Smith
Emma White
...

Can you please tell me what I should do differently? Thanks!

Pentium10 · Accepted Answer · 2015-05-05 07:11:36Z

19

You need to use CONCAT and the trim functions

SELECT CONCAT(rtrim(ltrim(first_name)),' ',rtrim(ltrim(last_name))) AS full_name
FROM
  (SELECT 'Anna' AS first_name,
          ' Miller ' AS last_name),

answered May 5, 2015 at 7:11

Pentium10

208k124 gold badges435 silver badges511 bronze badges

Thank you, it works in most of the cases but if the value for first_name or last_name is null, it returns null. I tried with string(first_name) but it stays null. I'd expect it to return e.g. Sean null or Sean NoName.
– Ilja
Commented May 5, 2015 at 7:45
2

You need to add IFNULL(first_name,'null'), IFNULL(first_name,'NoName')
– Pentium10
Commented May 5, 2015 at 8:19
2

Also SELECT RTRIM(LTRIM(first_name))+' '+RTRIM(LTRIM(last_name)) would work (+ instead of CONCAT)
– Felipe Hoffa
Commented May 5, 2015 at 21:15

Add a comment |

chaooder · Accepted Answer · 2020-03-23 15:52:11Z

4

You can also use the double pipe || concatenation operator for concatenation of strings (see here for more info):

select 
    trim(first_name) || ' ' || trim(last_name)
from
    ...

answered Mar 23, 2020 at 15:52

chaooder

1,5162 gold badges19 silver badges40 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Big query - Concatenate strings horizontally

2 Answers 2

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Related