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 recyclebinSQL> 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
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 stringSQL> -- 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 spaceSQL> 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(
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 togetherSQL> SELECT CONCAT('Oracle','Server')
AS Concat FROM Dual; CONCAT ------------ OracleServer
CONCAT() concatenates two (or more) stringsSQL> Syntax: CONCAT(
DUMP Union all with dump reverseSQL> 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,c3String||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 formatSQL> 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
TRANSLATEall 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 numbersSQL> 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 decodeSQL> 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>
No comments:
Post a Comment