CODEWITHSUNDEEP
postgresqlchar
Stefan
Stefan

Reputation: 23

Count characters of multiple columns

I have a table with 15 columns (text).
I counted the number of characters for every column with char_length and stored it in a view. Now I want to sum up these results. In some fields of the table were is currently no text so these fields are are NULL/empty. If I now sum the result fields of the view (integer) only rows are calculated where all 15 columns are filled. 

SELECT C1+C2+...+C15 FROM view;

I tried also these approaches (without using the view): 

SELECT char_length(C1)+char_length(C2)+...+char_length(C15) FROM T1; 
SELECT char_length(C1||C2||...||C15) FROM T1;

How can I count characters of multiple columns (per row)? Even if some columns are currently not filled.

Upvotes: 0

Views: 1655

Answers (1)

user330315
user330315

Reputation:

The concat() function will treat a NULL value as an empty string:

select char_length(concat(c1, c2, c3, c4, c5))
from t1;

It will also implicitly cast any non-character value to a string, so even if one of the columns is e.g. an integer this will work.

Upvotes: 4

Related Questions