3

My data looks like :

id   name   age   gender
1    abc          M

'age' value is null.

I want to concatenate column value with separator '\n' and if one column value is null, then also use '\n' to represent it.

I tried concat_ws, but it only return :

1\nabc\nM

What I want is :

1\nabc\n\nM

That is it should be 2 '\n' after 'abc'.

How can I achieve my requirement?

informatik01
  • 16,038
  • 10
  • 74
  • 104
frank
  • 1,169
  • 18
  • 43

3 Answers3

1
select concat_ws('\n', id, coalesce(name,''), coalesce(age,''), coalesce(gender,''))
from your_table
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 1
    Doesn't that return `null` if `age` is `null`? –  Jan 12 '15 at 07:35
  • @juergen d, this works. But actually we have a great number of column, so if we add 'coalesce' for each column, it seems that this sql statement is complex and not clear. Do you have better idea? – frank Jan 12 '15 at 08:10
1

No need to use concat_ws,It will not concat that value if it is null.use concat +ifnull function

select concat('\n', ifnull(id,''),ifnull(name,''),ifnull(age,''),ifnull(gender,'')) 
from your_table
Charvee Shah
  • 730
  • 1
  • 6
  • 21
1

You should probably use ifnull(column_name,'') to replace the null column with empty character and try concat_ws.

select concat_ws('\n',id,name,ifnull(age,''),gender) from table1;

SQL Fiddle link: http://sqlfiddle.com/#!2/12074/10

Logan
  • 1,331
  • 3
  • 18
  • 41
  • because we don't know which column has null value, so we have to use 'ifnull' in each column in your solution such as 'select concat_ws('\n',id,ifnull(name,''),ifnull(age,''),ifnull(gender,'') from table1'. Actually, our table has a great number of column, so there is a great number of 'ifnull' for each column. This looks like complex and not clearly. Do you have any better idea? – frank Jan 12 '15 at 08:08
  • functions do not generally accept dynamic columns. In this case ifnull can only be applied to columns one by one. – Logan Jan 12 '15 at 08:46