в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:11:34Z

1 версия

Новая страница

== Character Functions==

Character Functions accept character input.
The input may come from a column in a table or from any expression.
Character Functions List
<OL><LI>ASCII(x) returns the ASCII value of the character x.</LI><LI>CHR(x) returns the character with the ASCII value of x.</LI><LI>CONCAT(x, y) concatenates y to x and return the appended string.</LI><LI>INITCAP(x) converts the initial letter of each word in x to uppercase and returns that string.</LI><LI>INSTR(x, find_string [, start] [, occurrence]) searches for find_string in x and returns the position at which find_string occurs.</LI><LI>INSTRB(x) returns the location of a string within another string, but returns the value in bytes for a single-byte character system.</LI><LI>LENGTH(x) returns the number of characters in x.</LI><LI>LENGTHB(x) returns the length of a character string in bytes, except that the return value is in bytes for single-byte character sets.</LI><LI>LOWER(x) converts the letters in x to lowercase and returns that string.</LI><LI>LPAD(x, width [, pad_string]) pads x with spaces to left, to bring the total length of the string up to width characters.</LI><LI>LTRIM(x [, trim_string]) trims characters from the left of x.</LI><LI>NANVL(x, value) returns value if x matches the NaN special value (not a number), otherwise x is returned.</LI><LI>NLS_INITCAP(x) Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.</LI><LI>NLS_LOWER(x) Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.</LI><LI>NLS_UPPER(x) Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.</LI><LI>NLSSORT(x) Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.</LI><LI>NVL(x, value) returns value if x is null; otherwise, x is returned.</LI><LI>NVL2(x, value1, value2) returns value1 if x is not null; if x is null, value2 is returned.</LI><LI>REPLACE(x, search_string, replace_string) searches x for search_string and replaces it with replace_string.</LI><LI>RPAD(x, width [, pad_string]) pads x to the right.</LI><LI>RTRIM(x [, trim_string]) trims x from the right.</LI><LI>SOUNDEX(x) returns a string containing the phonetic representation of x.</LI><LI>SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.</LI><LI>SUBSTRB(x) Same as SUBSTR except the parameters are expressed in bytes instead of characters to handle single-byte character systems.</LI><LI>TRIM([trim_char FROM) x) trims characters from the left and right of x.</LI><LI>UPPER(x) converts the letters in x to uppercase and returns that string.</LI></OL>
11. 1. Introduction
11. 1. 1.
Character Functions
11. 1. 2.
<A href="/Tutorial/Oracle/0220__Character-String-Functions/CombiningFunctions.htm">Combining Functions</a>

== Combining Functions==

You can use any valid combination of functions in a SQL statement.
The following example combines the UPPER() and SUBSTR() functions;
The output from SUBSTR() is passed to UPPER().



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> SELECT first_name, UPPER(SUBSTR(first_name, 2, 2)) FROM employee;
FIRST_NAME UP
---------- --
Jason AS
Alison LI
James AM
Celia EL
Robert OB
Linda IN
David AV
James AM
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Character String Functions/Introduction - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия