2

This is my table:

id| name  | address | more_info
_______________________________
0 | John  | NY      | infoa
1 | Bill  | PH      | infob
2 | Bob   | KS      | infoc

Where id type is BIGINT.

I have this query:

SELECT * FROM myTable

BIGINT is too long for some applications, thus I want to retrieve the id as a VARCHAR. Can I cast the id to VARCHAR in the result set without having to name each specific column I want to retrieve in the result set? I.E, I want to keep the actual query SELECT *, and not have to put SELECT id, name, address etc.

MeLight
  • 5,454
  • 4
  • 43
  • 67
  • possible duplicate of [Cast int to varchar](http://stackoverflow.com/questions/15368753/cast-int-to-varchar) – Dan Smith Jan 19 '15 at 17:19

1 Answers1

6

Simple answer: no. You need some transformation stage/layer between your table and your expected result. You could have it with a view that casts integers to varchars, but since you are reluctant to introduce the coupling into select, I bet the view would be even worse.

On the other hand, there might be a chance on the app side, depending on the language.

edit: If you can live having one additional column and one to ignore (the original id), maybe this would help:

select CAST(id as CHAR(50)) as idAsVarchar, t.* FROM
(
    select * from your_table
) as t;
ptrk
  • 1,800
  • 1
  • 15
  • 24
  • I don't mind introducing coupling (I think), I just don't want to name each column I want extract except for the cast column, because I have around 40. Is this achievable? – MeLight Jan 20 '15 at 08:40
  • That did the trick, thanks! But I had to add `as t` at the end of the query for it work ;) Consider editing your answer. – MeLight Jan 20 '15 at 17:58
  • 7
    The subquery is redundant `SELECT *, CAST(id AS CHAR(50)) AS idAsVarChar FROM your_table` works also – Gervs Jan 20 '15 at 18:41
  • Actually, when I look at my answer, it is pretty damn obvious. Good point! – ptrk Jan 20 '15 at 18:47