On psql
select titolo,lingua from titolo where titolo ~* 'brivid';
titolo | lingua
------- + ------
Brivido | 1
On Sqlplus/gqsql
SQL> select titolo,genere,anno,lingua from titolo where titolo like '%rivid%';
TITOLO -------------------------------------------------------------------------------- GENERE ANNO LINGUA ---------- ---- ----------
Brivido 1 1986 1
How can format the sql output on sqlplus to do something similar like mysql or psql?
Thanks
If you use xigole jisql, you get the same presentation from all JDBC databases, and all JDBC drivers are available and free. There is also unixODBC isql and the various unixODBC drivers.
sqlplus: SQL*Plus Command Reference
SET COLSEP {_|text}
In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output
that is rendered inside tags. HTML table output is the default. To generate preformatted output you must set
PREFORMAT ON with the SET MARKUP HTML PREFORMAT ON command.
Sets the text to be printed between selected columns. If the COLSEP variable contains blanks or punctuation
characters, you must enclose it with single quotes. The default value for text is a single space.
In multi-line rows, the column separator does not print between columns that begin on different lines. The
column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the
record separator. See SET RECSEP in this chapter for more information.
Example
To set the column separator to "|" enter
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;
LAST_NAME |JOB_ID |DEPARTMENT_ID
-------------------------|----------|-------------
Hartstein |MK_MAN | 20
Fay |MK_REP | 20
I have tried..
SQL> set COLSEP "|"
SQL> select titolo,anno from titolo where titolo = 'Brivido';
TITOLO
--------------------------------------------------------------------------------
ANNO
----
Brivido
1986
What made you choose double quotes? In SQL they are very different in optional ways.
Double quotes is just fine.
Increase your linesize to 9000 and set colsep to |
set linesize 9000 colsep |
Use SQL*Plus COLUMN format:
column titolo format a30
column anno format a20
Run your query:
select titolo,anno from titolo where titolo = 'Brivido';
Note: Adjust column width as per your field size.
Works fine,thanks
Yes, sqlplus is much fussier than isql, can get into problems with big rows, manually set buffers and lacks speed. I am guessing Oracle did not want it to be friendly to scripting, and loaded it with appearance options for small scale ad hoc query. Luckily, you can jump ship to ODBC or JDBC and isql or jisql. Some like Toad, but I like SQuirreL, where skills transfer between RDBMS.