oracle字串函式（轉）

cosio發表於2007-08-02
Oracle字串函式
oracle字串函式[@more@]
Get The ASCII Value Of A CharacterASCII(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
SELECT ASCII('A') FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;
Upper CaseUPPER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT UPPER('Dan Morgan') FROM dual;
Lower CaseLOWER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT LOWER('Dan Morgan') FROM dual;
Initial Letter Upper CaseINITCAP(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT INITCAP('DAN MORGAN') FROM dual;
NLS Upper CaseNLS_UPPER()
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
NLS Lower CaseNLS_LOWER()
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;
NLS Initial Letter Upper CaseNLS_INITCAP()
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;
CharacterCHR(n PLS_INTEGER) RETURN VARCHAR2;
SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;

SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual;
Returns the first non-null occurrenceCOALESCE(, , , ...)
CREATE TABLE test (
col1 VARCHAR2(1),
col2 VARCHAR2(1),
col3 VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT COALESCE(col1, col2, col3) FROM test;
Concatenate (overload 1)CONCAT(left IN VARCHAR2, right IN VARCHAR2) RETURN VARCHAR2
SELECT CONCAT('Dan ', 'Morgan') FROM dual;
Concatenate (overload 2)CONCAT(left IN CLOB, right IN CLOB) RETURN CLOB
set serveroutput on

DECLARE
c1 CLOB := TO_CLOB('Dan ');
c2 CLOB := TO_CLOB('Morgan');
c3 CLOB;
BEGIN
SELECT CONCAT('Dan ', 'Morgan')
INTO c3
FROM dual;

dbms_output.put_line(c3);
END;
/
Converts From One Character Set To AnotherCONVERT(,,
)
SELECT CONVERT('Ġʠ͠ՠؠA B C D E','US7ASCII','WE8ISO8859P1')
FROM dual;
Returns The Number Of Bytes And Datatype Of A ValueDUMP(, , , );
8Octal
10Decimal
16Hexidecimal
17Single Characters
1008octal notation with the character set name
1010decimal notation with the character set name
1016hexadecimal notation with the character set name
1017single characters with the character set name
set linesize 121
col dmp format a50

SELECT table_name, DUMP(table_name) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM user_tables;
INSTR
See links at page bottom
Location of a string, within another string, in bytesINSTRB(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRB('Dan Morgan', ' ', 1, 1) FROM dual;
Location of a string, within another string, in Unicode complete charactersINSTRC(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRC('Dan Morgan', ' ', 1, 1) FROM dual;
Location of a string, within another string, in UCS2 code pointsINSTR2(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR2('Dan Morgan', ' ', 1, 1) FROM dual;
Location of a string, within another string, in UCS4 code pointsINSTR4(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR4('Dan Morgan', ' ', 1, 1) FROM dual;
String LengthLENGTH()
SELECT LENGTH('Dan Morgan') FROM dual;
Returns length in bytesLENGTHB()
SELECT table_name, LENGTHB(table_name) FROM user_tables;
Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.
Left Pad

Overload 1		LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER,
PAD VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25) FROM dual;
Overload 3LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len NUMBER,
PAD CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Left Trim

Overload 1		LTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || LTRIM(' Dan Morgan ') || '
Overload 2LTRIM(
STR1 VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || LTRIM('xxx Dan Morgan ') || '
SELECT '->' || LTRIM('xxxDan Morgan ', 'x') || '
The Maximum String based on the current sort parameterMAX()
SELECT MAX(table_name)
FROM user_tables;
The Minimum String based on the current sort parameterMIN()
SELECT MIN(table_name)
FROM user_tables
Returns the string of bytes used to sort a string.

The string returned is of RAW data type		NLSSORT(, 'NLS_SORT = );
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('G⢥rd');
COMMIT;

SELECT * FROM test ORDER BY name;

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = BINARY_CI');
q used to define a quote delimiter for PL/SQLq'';
set serveroutput on

DECLARE
s1 VARCHAR2(20);
s2 VARCHAR2(20);
s3 VARCHAR2(20);
BEGIN
s1 := q'[Isn't this cool]';
s2 := q'"Isn't this cool"';
s3 := q'|Isn't this cool|';

dbms_output.put_line(s1);
dbms_output.put_line(s2);
dbms_output.put_line(s3);
END;
/
REPLACE
See links at page bottom
ReverseREVERSE()
SELECT REVERSE('Dan Morgan') FROM dual;

SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;
Right Pad

Overload 1		RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER,
pad VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25) ||'
Right Trim

Overload 1		RTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || RTRIM(' Dan Morganxxx') || 'SELECT '->' || RTRIM(' Dan Morganxxx', 'xxx') || '
Overload 2RTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || RTRIM(' Dan Morgan ') || '

Returns Character String Containing The Phonetic Representation Of Another String		Rules:
  • Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y
  • Assign numbers to the remaining letters (after the first) as
    follows:
    b, f, p, v = 1
    c, g, j, k, q, s, x, z = 2
    d, t = 3
    l = 4
    m, n = 5
    r = 6
  • If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
  • Return the first four bytes padded with 0.

SOUNDEX(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;

CREATE TABLE test (
namecol VARCHAR2(15));

INSERT INTO test (namecol) VALUES ('Smith');
INSERT INTO test (namecol) VALUES ('Smyth');
INSERT INTO test (namecol) VALUES ('Smythe');
INSERT INTO test (namecol) VALUES ('Smither');
INSERT INTO test (namecol) VALUES ('Smidt');
INSERT INTO test (namecol) VALUES ('Smick');
INSERT INTO test (namecol) VALUES ('Smiff');
COMMIT;

SELECT name, SOUNDEX(namecol) FROM test;
-- Thanks Frank van Bortel for the idea for the above

SELECT *
FROM test
WHERE SOUNDEX(namecol) = SOUNDEX('SMITH');
SUBSTR
See links at page bottom
Returns a substring counting bytes rather than charactersSUBSTRB(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page
Returns a substring within another string, using Unicode code pointsSUBSTRC(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page
Returns a substring within another string, using UCS2 code pointsSUBSTR2(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page
Returns a substring within another string, using UCS4 code pointsSUBSTR4(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See Demos on the Substring Page
TRANSLATE
See links at page bottom
Changes The Declared Type Of An ExpressionTREAT ( AS REF schema.type))
SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p;
Trim SpacesTRIM()
SELECT ' Dan Morgan ' FROM dual;

SELECT TRIM(' Dan Morgan ') FROM dual;
Trim Other CharactersTRIM( FROM )
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
Trim By CHR valueTRIM()
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;

SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;
Also known as Pipes ||
SELECT 'Dan' || ' ' || 'Morgan' FROM dual;

with alias

SELECT 'Dan' || ' ' || 'Morgan' NAME FROM dual;
or
SELECT 'Dan' || ' ' || 'Morgan' AS NAME FROM dual;
Byte SizeVSIZE(e IN VARCHAR2) RETURN NUMBER
SELECT VSIZE('Dan Morgan') FROM dual;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/101162/viewspace-930849/，如需轉載，請註明出處，否則將追究法律責任。

相關文章