MySQL Forums
Forum List  »  Newbie

Select * with a function applied to one column
Posted by: Dean Richert
Date: December 03, 2014 01:16AM

What is the most elegant way to use a SELECT * statement but have one (or more) rows have a function used on them before they are returned? Here is my attempt, which works, but can it be improved?

SELECT geo.*, INET_NTOA(ip) AS converted_ip FROM geo WHERE uid = 100;
The catch is that it would be vastly preferable to me to have the column that has the function performed a column with the same name as the column the function was performed on, instead of FUNCTION([column_name]). (So that column ip is returned as column ip, not column INET_NTOA(ip)) I tried:

SELECT geo.*, INET_NTOA(ip) AS ip FROM geo WHERE uid = 100;

but I receive two columns named ip in my result set, which would be very confusing to deal with.

The first statement was my best attempt but it requires changing all of my non mysql to look for the converted_ip column and not the ip column. Is that the best I can do, or is there another way? My ideal result would just replace the column ip in the result set with the result of INET_NTOA(ip).

I could write out every column, but this would be hard to maintain, as there are many columns and I actually do make use of the value in every field (so the SELECT * is warranted).

Options: ReplyQuote


Subject
Written By
Posted
Select * with a function applied to one column
December 03, 2014 01:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.