How to select all columns while trimming the result of one of them?

Question

I have a table

This query will select all columns as they are.

select * from t1

But I need to trim one of those columns.

Can I do it this way?

select * 
  case 
    when column4 is not null
      then substr(column4, - 5)
from table

Or do I need

select * 
  case 
    when column4 is not null
      then column4 = substr(column4, - 5)
from table

The result should have all columns exept column4 unchanged.

Don't be lazy, specify them all! (But you can skip the case, simply do just the substring part.) — jarlh, Dec 02 '16 at 10:58
@jar there are more than 20 columns in a table and more than 10 tables. I will need to specify 200 columns at the very least. Isn't there a better way? — gjin, Dec 02 '16 at 11:01
It can be a bit of a pain but always prefer a column list, [select * vs select column](http://stackoverflow.com/questions/3180375/select-vs-select-column) — Alex K., Dec 02 '16 at 11:03

score 0 · Answer 1 · answered Dec 02 '16 at 11:00

0

There's no operator for "all columns except something", unfortunately. You'd have to specify all of them:

SELECT column1, column2, column3, SUBSTRING(column4, LENGTH(column4) - 5), etc
FROM   mytable

answered Dec 02 '16 at 11:00

Mureinik

297,002
52
306
350

score 0 · Answer 2 · answered Dec 02 '16 at 11:08

0

syntax should be like this:

select column1,
       column2,
       column3,
       case 
       when column4 is not null 
        then substring(column4, length(column4) - 5)
       end column4 ,
       column5
  from table;

answered Dec 02 '16 at 11:08

TheName

697
1
7
18

score 0 · Answer 3 · answered Dec 02 '16 at 15:02

It depends a bit on what you want to accomplish. This works:

select substr(column4, 0, 5), t1.* from sometable t1;

However, it returns column4 twice. Once with 5 characters (use length as in the other answers if you want to discard the last 5 rather than using a fixed length, not clear what your goal is) and once with all characters.

You could also generate the SQL with PL/SQL using user_tab_columns. If you just want to save yourself some typing something like this might work:

declare
  v_sql varchar2(1024) := null;
begin
  for rec in (select column_name, table_name from user_tab_columns
    where table_name = 'TEST1') loop
    if v_sql is null then
      v_sql := 'select ';
    else
      v_sql := v_sql || ',';
    end if;   
    if rec.column_name = 'C2' then
      v_sql := v_sql || ' substr(' || rec.table_name || '.c2, 0, 5)';
    else
      v_sql := v_sql || rec.table_name || '.' || rec.column_name;
    end if;
  end loop;
  v_sql := v_sql || ' from test1';
  dbms_output.put_line(v_sql);
end;
/

No points for code style, but it works and prints the SQL. Add more special cases if you need to truncate multiple columns and add the other tables as needed.

How to select all columns while trimming the result of one of them?

3 Answers3