sql - Getting first non-NULL and non-EMPTY value with COALESCE() - Stack Overflow

admin管理员组

文章数量:1384613

I'm working on MariaDB 11.7.2. I have a list of columns as source_columns and a target column as target_column, dynamically given. I want to get the first non-NULL and non-empty value from the source columns and update the target column with that value.

Sample data:

source_columns = col1, col2, col3

col1, col2, col3
NULL, 12345, NULL
223, NULL, ""
"", 556, 2234

In the above example, some entries (noted with double quotes) are empty. as in col = "".

The expected output is:

12345
223
556

I have the following query so far:

SET @sql = CONCAT(
    'UPDATE `', table_name, '` SET `', target_column, '` = COALESCE(',
    REPLACE(source_columns, ',', ', '),
    ')'
);

This one skips the NULL values but does not skip the empty values in the source columns. I am trying the following but could not make it work.

SET @sql = CONCAT(
    'UPDATE `', table_name, '` SET `', target_column, '` = COALESCE(NULLIF(`', REPLACE(source_columns, ',', '`, ""), NULLIF(`'), '`, ""))'
);

Any ideas how to fix it?

Thanks!

I'm working on MariaDB 11.7.2. I have a list of columns as source_columns and a target column as target_column, dynamically given. I want to get the first non-NULL and non-empty value from the source columns and update the target column with that value.

Sample data:

source_columns = col1, col2, col3

col1, col2, col3
NULL, 12345, NULL
223, NULL, ""
"", 556, 2234

In the above example, some entries (noted with double quotes) are empty. as in col = "".

The expected output is:

12345
223
556

I have the following query so far:

SET @sql = CONCAT(
    'UPDATE `', table_name, '` SET `', target_column, '` = COALESCE(',
    REPLACE(source_columns, ',', ', '),
    ')'
);

This one skips the NULL values but does not skip the empty values in the source columns. I am trying the following but could not make it work.

SET @sql = CONCAT(
    'UPDATE `', table_name, '` SET `', target_column, '` = COALESCE(NULLIF(`', REPLACE(source_columns, ',', '`, ""), NULLIF(`'), '`, ""))'
);

Any ideas how to fix it?

Thanks!

• sql
• mariadb

Share Improve this question Follow edited Mar 17 at 20:24 yenren asked Mar 17 at 19:51 yenrenyenren 5221010 silver badges2222 bronze badges 10

• 1 Does MariaDB have NULLIF()? You can use it to make it treat an empty value as if it were NULL for the larger COALESCE() call. Ie: COALESCE(NULLIF(col1,''), NULLIF(col2, '') ... ) Assumes you have string (varchar) columns. – Joel Coehoorn Commented Mar 17 at 20:13
• 1 According to this small fiddle, your last SET runs perfectly fine. Could you provide a complete example where it does not work? – Guillaume Outters Commented Mar 17 at 21:02
• 1 ah, it does fail with "col1, col2, col3" instead of "col1,col2,col3". replace the spaces if needed. – ysth Commented Mar 18 at 0:14
• 1 @GuillaumeOutters you did most of the work, feel free to answer. just needs replace(replace(source_columns,' ',''), ',', ... – ysth Commented Mar 18 at 13:52
• 1 @yenren for your next question, please provide your sample data as table create & insert statements; it would have sped up getting you an answer greatly – ysth Commented Mar 18 at 14:22

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 0

The whitespace problem

As discussed in the comments notably by @ysth,
your 2nd query is right… except for the sanitizing.

When input with a source_columns of col1, col2, col3,
the spaces get included in the resulting query:

UPDATE `t` SET `dest` = COALESCE(NULLIF(`col1`, ""), NULLIF(` col2`, ""), NULLIF(` col3`, ""));

which looks for table named col2 and col3 with an (easy to miss) space in their name.

The solution

Simply replace your raw source_columns by REPLACE(source_columns, ' ', '') to purge those dangerous spaces:

SET @sql = CONCAT(
    'UPDATE `', table_name, '` SET `', target_column, '` = COALESCE(NULLIF(`', REPLACE(REPLACE(source_columns, ' ', ''), ',', '`, ""), NULLIF(`'), '`, ""))'
);

And it runs marvelously.

本文标签： sqlGetting first nonNULL and nonEMPTY value with COALESCE()Stack Overflow