Set Column

SQL> column ename   heading "Employee"
SQL> column sal     heading "Salary"
SQL> column raise   heading "Raise"

column department_name format a30 heading "Department Names"

column ename format a13 heading "Employee Name"

 column department_name format a13
SQL>  column employees format a63 word_wrapped  

SQL> COLUMN product_price FORMAT $99.99  

SQL>> SET SQLPROMPT "QUIUM>>" 

SQL> --Set the prompt. Use predefined variables to access login
SQL> --user name and net service name
SQL> SET SQLPROMPT "&_user@&_connect_identifier(&database_name) >"

JAVA2S@XE(XE) >SET TERMOUT ON

JAVA2S@XE(XE) >SET FEEDBACK ON 

show parameters block 

A dropped table is preserved in the recycle bin until purged. 

 purge recyclebin; 

select object_name, original_name, type from recyclebin; 

--------------------------------------------------------

purge recyclebin
SQL> SQL> purge recyclebin; Recyclebin purged. SQL> drop table vacation; SQL> SQL> select object_name, original_name, droptime 2 from recyclebin; no rows selected SQL> SQL> flashback table vacation to before drop;


---------------------------------------------------------

 SQL>>conn sqlplus / as sysdba

SQL>> conn sqlplus sys/password as sysdba 

set pagesize 10;
SQL>
SQL> show feedback;

 set linesize 54

 SET PAGESIZE 0
SQL> SET FEEDBACK OFF
SQL> SELECT emp_id || ',"'
  2         || ename || '",' ||
  3         rate
  4  FROM emp;

 SQL> SET FEEDBACK ON

LTRIM 

- Ltrim: removes a set of characters from the left of a string.
SQL>
SQL>
SQL> -- LTRIM stands for "left trim."
SQL> -- The general format for this function is:
SQL>
SQL> -- LTRIM(string, characters_to_remove)
SQL>
SQL> SELECT LTRIM('...Mitho', '.') FROM dual;

LTRIM
-----
Mitho
 

LTRIM function removes leading characters
SQL> SQL> --LTRIM function removes leading characters SQL> SQL> SELECT LTRIM('ST' , 'STEVENS') AS TRIM FROM Dual; T -



select '"' || ltrim( ' Some String' ) || '"' "A String"

  
SQL>
SQL> select '"' || ltrim( '    Some String' ) || '"' "A String" from dual;
A String
-------------
"Some String"

1 row selected.

LTRIM('ABCBCA12345', 'ABC')
SQL> SQL> SELECT LTRIM('ABCBCA12345', 'ABC') 2 FROM DUAL; LTRIM ----- 12345

 select ename
  2  ,      ltrim(ename,'S') as ltrim_s
  3  ,      rtrim(ename,'S') as rtrim_s
  4  from   emp
  5  where  deptno = 20;

ENAME    LTRIM_S  RTRIM_S
-------- -------- --------
Tom      Tom      Tom
Jane     Jane     Jane
Smart    mart     Smart
Ana      Ana      Ana
Fake     Fake     Fake

 RTRIM

Rtrim: removes a set of characters from the right of a string
SQL> -- RTRIM stands for "right trim."SQL> -- The general format for this function is: SQL> SQL> -- RTRIM(string, characters_to_remove) SQL> SQL> SQL> SELECT RTRIM('Computers', 's') FROM dual; RTRIM('C -------- Computer

RTRIM function removes trailing characters

SQL> --RTRIM function removes trailing characters.
SQL>
SQL> SELECT RTRIM('S' , 'STEVENS') AS TRIM FROM Dual;

T
-

Use rtim to remove space
SQL> SQL> select '"' || rtrim( 'Some String ' ) || '"' "A String" 2 from dual 3 / A String ------------- "Some String" 1 row selected.

 Rtrim char type column data

SELECT 'Item  ' ||
  2         RTRIM(item_id) ||
  3         ' is described as a ' ||
  4         RTRIM(item_desc) ||
  5         '.'  "Item Description Sentence"
  6  FROM   old_item;

Item Description Sentence
-----------------------------------------------------------------------
Item  LA-101 is described as a Can, Small.
Item  LA-102 is described as a Can, Large.

 CHR

CHR() function returns a character corresponding to the number passed in as the argument
SQL> Syntax: CHR( [USING NCHAR_CS]) SQL> 
SQL>
 SELECT 2 CHR(65) "DB_CS", 3 CHR(65 USING NCHAR_CS) "N_CS" 4 FROM dual; D N - - A A

SQL> select chr(71)||chr(114)||chr(111)||chr(111)||chr(118)||chr(121) "Cool!"
  2    from dual;

Cool!
------
Groovy


CONCAT

Simple demo for CONCAT function: concatenate two strings together
SQL> SELECT CONCAT('Oracle','Server')


AS Concat FROM Dual; CONCAT ------------ OracleServer

CONCAT() concatenates two (or more) strings
SQL> Syntax: CONCAT(,) SQL> SQL> SELECT 2 CONCAT('Hello ', ' ,world') result from dual; RESULT ---------------------------------------------------------------- Hello ,world

 DUMP

Union all with dump reverse
SQL> SQL> SQL> select 90101, dump(reverse(90101),16) from dual 2 union all 3 select 90102, dump(reverse(90102),16) from dual 4 union all 5 select 90103, dump(reverse(90103),16) from dual 6 / 90101 DUMP(REVERSE(90101),1 ---------- --------------------- 90101 Typ=2 Len=4: 2,2,a,c3 90102 Typ=2 Len=4: 3,2,a,c3 90103 Typ=2 Len=4: 4,2,a,c3 3 rows selected.

Syntax: DUMP(expression [,RETURN_FORMAT [START_POSITION] [LENGTH]])
SQL> SQL> SELECT 2 DUMP('ABC',1010) type_info 3 FROM dual;



START_POSITION specifies which character in the supplied string to start with, and the optional length specifies how many characters to consider.

  
SQL>
SQL> SELECT
  2     DUMP('ABC',1010,1)  type_info
  3  FROM dual;

TYPE_INFO
------------------------------------------------
Typ=96 Len=3 CharacterSet=WE8MSWIN1252: 65,66,67

   
  
dump table column

select dump(emp_id,8) from emp_mast;

dump(90101,16)
SQL> SQL> SQL> select 90101, dump(90101,16) from dual 2 union all 3 select 90102, dump(90102,16) from dual 4 union all 5 select 90103, dump(90103,16) from dual 6 / 90101 DUMP(90101,16) ---------- --------------------- 90101 Typ=2 Len=4: c3,a,2,2 90102 Typ=2 Len=4: c3,a,2,3 90103 Typ=2 Len=4: c3,a,2,4




dump(reverse(90101),16)

   

SQL>
SQL>
SQL> select 90101, dump(reverse(90101),16) from dual
  2  union all
  3  select 90102, dump(reverse(90102),16) from dual
  4  union all
  5  select 90103, dump(reverse(90103),16) from dual
  6  /

     90101 DUMP(REVERSE(90101),1
---------- ---------------------
     90101 Typ=2 Len=4: 2,2,a,c3
     90102 Typ=2 Len=4: 3,2,a,c3

String||String: concatenates two strings

SQL> -- String||String
SQL>
SQL> -- This function concatenates two strings.
SQL>
SQL> -- String||String
SQL> SELECT 'This' || ' is '|| 'a' || ' concatenation' FROM dual;

'THIS'||'IS'||'A'||'CON
-----------------------
This is a concatenation

Converting DATE to another language

SQL>
SQL>
SQL> -- Converting DATE to another language.
SQL> SELECT TO_CHAR(SYSDATE,'MONTH DD YY','NLS_DATE_LANGUAGE=german') "German Date" from DUAL;

German Date
---------------
AUGUST    31 06

Converting German date to DATE format
SQL> SQL> -- Converting German date to DATE format. SQL> SELECT TO_DATE('februar-23','MONTH-DD','NLS_DATE_LANGUAGE=german') "Converted" from DUAL; Converted --------- 23-FEB-06

TRANSLATE

all dashes ("-") were dropped from the translated string, and the digit "9" was translated into an asterisk.

 
SQL>
SQL> SELECT
  2       TRANSLATE('4428-2174-5093-1501'
  3                ,'0123456789-'
  4                ,'0123456789') numbers_only
  5  FROM dual;

NUMBERS_ONLY
----------------
4428217450931501

 

SQL>  update nchar_samples
  2       set char_data = translate( nchar_data using char_cs )

all dashes ("-") were dropped from the translated string, and the digit "9" was translated into an asterisk.
SQL> SQL> SELECT 2 TRANSLATE('4428-2174-5093-1501' 3 ,'0123456789-' 4 ,'0123456789') numbers_only 5 FROM dual; NUMBERS_ONLY ---------------- 4428217450931501

TRANSLATE(): convert numbers
SQL> SQL> -- TRANSLATE(): convert numbers SQL> SQL> SELECT TRANSLATE(12345, 2 54321, 3 67890) FROM dual; TRANS ----- 09876



Translate: replaces a string character by character

SQL> -- Translate: replaces a string character by character.
SQL>
SQL> -- RANSLATE(string, characters_to_find, characters_to_replace_by)
SQL> SELECT TRANSLATE('Mississippi', 's','S') FROM dual;

TRANSLATE('
-----------
MiSSiSSippi


           
       

Using the TRANSLATE() Function: to encode and decode
SQL> SQL> --Using the TRANSLATE() Function SQL> SQL> --TRANSLATE(x, from_string, to_string) to convert the occurrences of characters in from_string found in x to corresponding characters in to_string. SQL> SQL> --TRANSLATE(): shift each character in the string SECRET MESSAGE: MEET ME IN THE PARK by four places to the right: A becomes E, B becomes F SQL>

SQL> SELECT TRANSLATE('SECRET MESSAGE: MEET ME IN THE PARK', 2 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 3 'EFGHIJKLMNOPQRSTUVWXYZABCD') FROM dual; TRANSLATE('SECRETMESSAGE:MEETMEINTH ----------------------------------- WIGVIX QIWWEKI: QIIX QI MR XLI TEVO SQL> SQL> --select translate('www.java2s.com','wjavscom','abced123') from dual; SQL>