Currently working in SQLite and have the following table
id subject_1 subject_2 1 maths algebra 2 english <NA> 3 french speech
I would like to create a new column – which is the concatenation of the columns subject_1 and subject_2, however I have values within my subject_2 column – is there a way of concatenating the columns so that I can get the following table
id new_col 1 maths algebra 2 english 3 french speech
Now i have tried the two following methods – both of which did not work; concatenation using || and coalesce (which only takes the first non null value)
select id subejct_1 | ''| subject_2 as new_col from my_table
coalesce is not an option as i would like all string outputs together from both columns.
Is there a way of convering the NA values before hand to an empty string instead?
Advertisement
Answer
If you are sure that subject_1
does not contain nulls then use coalesce
only for subject_2
:
select id, subject_1 || COALESCE(' ' || subject_2, '') new_col from tablename
See the demo.
Results:
| id | new_col | | --- | ------------- | | 1 | maths algebra | | 2 | english | | 3 | french speech |