I have a table in sqlite and now I want to alias multiple columns, how can I do it?
For example, my columns are Name, Family, City and now I want to check a condition on them
SELECT (Name,Family,City) AS Personal From tblPersonal
WHERE Personal!=NULL
Updated answer:
Well if you want to check if all columns are NOT NULL
the easy solution would be to concating/adding then, with the +
operator:
SELECT Personal From
(SELECT (Name + Family + City) AS Personal From tblPersonal) AS SubQueryPersonal
WHERE Personal IS NOT NULL
Since concating/adding NULL
with the +
operator results in NULL
.
Just for completeness, if you want to use the columns you would have to add them, in both queries.
For example:
SELECT Personal, Name, Family, City From
(SELECT (Name + Family + City) AS Personal, Name, Family, City From tblPersonal) AS SubQueryPersonal
WHERE Personal IS NOT NULL
Update 2:
I made a small online Fiddle, where this can be seen in action, and I also added the suggestion of using the ||
operator from @JonasMetzler comment, so that it can be compared.
But since this new field, is only used for checking if all columns are NOT NULL
it should not matter, if you use +
or ||
.
While doing this, I found out, in SQLite it is possible to use Alias Names in the
WHERE
clause. It is non-SQL-Standard, but it works in SQLite, and like this you don't need a subquery. (Link to SO Post for details)
So this would be possible (in SQLite):
SELECT Name + Family + City AS Personal FROM tblPersonal WHERE Personal IS NOT NULL;
Old Answer is incorrect:
Even if I don't understand the why, theoretically you could build something similar (depending on your usecase you would need a different query)
But basically, you can create a subquery that use COALESCE
(link to the documenation) achieve something similar:
SELECT Personal From
(SELECT COALESCE (Name,Family,City) AS Personal From tblPersonal) AS SubQueryPersonal
WHERE Personal IS NOT NULL
Just to explain:
COALESCE
returns the first non-Null Argument, so it should have the desired effect.