Trim Trailing 0 from one colum in query Select *

Question

If in SELECT query name each column and for one column use TRIM(TRAILING '0' FROM ColumnName) AS ColumnName then ok.

But what if SELECT * and want to use Trim Trailing 0 for one column?

To use something like SELECT *, REPLACE(ColumnName, '0', '')? But need to replace only ending 0

It's not advisable to use `SELECT *` anyway. [Why is SELECT * considered harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — Itay, Sep 01 '13 at 08:09
OK. `SELECT *` is bad. What is alternative? Simply name each necessary column? — user2465936, Sep 01 '13 at 08:28

score 0 · Answer 1 · answered Sep 01 '13 at 08:10

0

select *.TRIM(TRAILING '0' from ColumnName) AS ColumnName

answered Sep 01 '13 at 08:10

mostafa khansa

302
1
6

Did you try this somewhere? I can't find this kind of syntax anywhere. – Itay Sep 01 '13 at 08:12
@Meabed is this a comment for me or mostafa? – Itay Sep 01 '13 at 08:30
Unfortunately can not get it to work. Initial query was `SELECT * FROM ( SELECT * , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal) DATA ` Changed to `SELECT *.TRIM(TRAILING '0' from ExchangeRate) AS ExchangeRate FROM ( SELECT * , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal) DATA ` And no result, no errors. – user2465936 Sep 01 '13 at 08:37

score 0 · Accepted Answer · edited Sep 01 '13 at 13:31

Try:

SELECT data.*,
       TRIM(TRAILING '0' from ExchangeRate) AS ExchangeRateTrimmed
FROM (.
  ... the subquery ...
) data

Hovever, this query returs all columns from the subquery (including ExchangeRate column) plus a new, additional column - ExchangeRateTrimmed.

Unfortunately ANSI SQL standard doesn't have a shotrtcut using '*' with the semantic "give me all columns, but modify only one of them" nor "give me all columns except one".

For now SELECT * means "give me all columns - as they are in the table (or subquery), without the ability to change them".

If you want to change one column using an expression, then you must list all of the others columns in the SELECT clause:

SELECT col1,
       col2,
       .....
       expression on col X [as col_name],
       ....
       colN
FROM table

Yes, I listed all necessary columns (taking into account that `SELECT *` is not good option). — user2465936, Sep 01 '13 at 15:46

Trim Trailing 0 from one colum in query Select *

2 Answers2