Oracle/PLSQL String Functions
| ASCII |
| Get The ASCII Value Of A Character | ASCII(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; |
| CASE Related Functions |
| Upper Case | UPPER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET; |
| SELECT UPPER('Dan Morgan') FROM dual; |
| Lower Case | LOWER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET; |
| SELECT LOWER('Dan Morgan') FROM dual; |
| Initial Letter Upper Case | INITCAP(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET; |
| SELECT INITCAP('DAN MORGAN') FROM dual; |
| NLS Upper Case | NLS_UPPER(<string_or_column>) |
| SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual; |
| NLS Lower Case | NLS_LOWER(<string_or_column>) |
| SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual; |
| NLS Initial Letter Upper Case | NLS_INITCAP(<string_or_column>) |
| SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual; |
| CHR |
| Character | CHR(n PLS_INTEGER) RETURN VARCHAR2; |
| SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;
SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual; |
| COALESCE |
|
Returns the first non-null occurrence
| COALESCE(<value>, <value>, <value>, ...) |
| 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; |
| CONCAT |
| Concatenate Overload 1 | standard.CONCAT(
lef VARCHAR2 CHARACTER SET ANY_CS,
right VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET LEFT%CHARSET; |
| 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(c1, c2)
INTO c3
FROM dual;
dbms_output.put_line(c3);
END;
/ |
| CONVERT |
| Converts From One Character Set To Another | CONVERT(<character>,<destination_character_set>,
<source_character_set>) |
| SELECT CONVERT('� � � � � A B C D E','US7ASCII','WE8ISO8859P1')
FROM dual; |
| DUMP |
|
Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value
| DUMP(<value> [,<return_format>[,<start_position>[,<length>]]]) | | | | 8 | Octal | | 10 | Decimal | | 16 | Hexidecimal | | 17 | Single Characters | | 1008 | octal notation with the character set name | | 1010 | decimal notation with the character set name | | 1016 | hexadecimal notation with the character set name | | 1017 | single 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 |
| INSTRB |
| Location of a string, within another string, in bytes | INSTRB(
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; |
| INSTRC |
| Location of a string, within another string, in Unicode complete characters | INSTRC(
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; |
| INSTR2 |
| Location of a string, within another string, in UCS2 code points | INSTR2(
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; |
| INSTR4 |
| Location of a string, within another string, in UCS4 code points | INSTR4(
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; |
| LENGTH |
| String Length | LENGTH(<string_or_column>) |
| SELECT LENGTH('Dan Morgan') FROM dual; |
| LENGTHB |
| Returns length in bytes | LENGTHB(<char_varchar2_or_clob_value>) |
| SELECT table_name, LENGTHB(table_name) FROM user_tables; |
| Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available. |
| LPAD |
| 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 2 | LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; |
| SELECT LPAD('Dan Morgan', 25) FROM dual; |
| Overload 3 | LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len NUMBER,
PAD CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET; |
| TBD |
| Overload 4 | LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET; |
| TBD |
| LTRIM |
| 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 ') || '<-' FROM dual; |
| Overload 2 | LTRIM(
STR1 VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; |
| SELECT '->' || LTRIM('xxx Dan Morgan ') || '<-' FROM dual;
SELECT '->' || LTRIM('xxxDan Morgan ', 'x') || '<-' FROM dual; |
| MAX |
| The Maximum String based on the current sort parameter | MAX(<character_string>) |
| SELECT MAX(table_name)
FROM user_tables; |
| MIN |
| The Minimum String based on the current sort parameter | MIN(<character_string>) |
| SELECT MIN(table_name)
FROM user_tables; |
| NLSSORT |
|
Returns the string of bytes used to sort a string.
The string returned is of RAW data type
| NLSSORT(<column_name>, 'NLS_SORT = <NLS Parameter>); |
| CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('G�berd');
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'); |
| Quote Delimiters |
|
q used to define a quote delimiter for PL/SQL
| q'<delimiter><string><delimiter>'; |
| 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 |
| REVERSE |
| Reverse | REVERSE(<string_or_column>) |
| SELECT REVERSE('Dan Morgan') FROM dual;
SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual; |
| RPAD |
| 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 2 | RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; |
| SELECT RPAD('Dan Morgan', 25) ||'<-' FROM dual; |
| RTRIM |
| 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') || '<-' FROM dual;
SELECT '->' || RTRIM(' Dan Morganxxx', 'xxx') || '<-' FROM dual; |
| Overload 2 | RTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; |
| SELECT '->' || RTRIM(' Dan Morgan ') || '<-' FROM dual; |
| SOUNDEX |
|
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 |
| SUBSTRB |
| Returns a substring counting bytes rather than characters | SUBSTRB(
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 |
| SUBSTRC |
| Returns a substring within another string, using Unicode code points | SUBSTRC(
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 |
| SUBSTR2 |
| Returns a substring within another string, using UCS2 code points | SUBSTR2(
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 |
| SUBSTR4 |
| Returns a substring within another string, using UCS4 code points | SUBSTR4(
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 |
| TREAT |
| Changes The Declared Type Of An Expression | TREAT (<expression> AS REF schema.type)) |
| SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p; |
| TRIM (variations are LTRIM and RTRIM) |
| Trim Spaces | TRIM(<string_or_column>) |
| SELECT ' Dan Morgan ' FROM dual;
SELECT TRIM(' Dan Morgan ') FROM dual; |
| Trim Other Characters | TRIM(<character_to_trim> FROM <string_or_column>) |
| SELECT TRIM('D' FROM 'Dan Morgan') FROM dual; |
| Trim By CHR value | TRIM(<string_or_column>) |
| SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;
SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual; |
| Vertical Bars |
| Also known as Pipes | <first_string> || <second_string> |
| SELECT 'Dan' || ' ' || 'Morgan' FROM dual;
with alias
SELECT 'Dan' || ' ' || 'Morgan' NAME FROM dual;
or
SELECT 'Dan' || ' ' || 'Morgan' AS NAME FROM dual; |
| VSIZE |
| Byte Size | VSIZE(e IN VARCHAR2) RETURN NUMBER |
| SELECT VSIZE('Dan Morgan') FROM dual; |
Nenhum comentário :
Postar um comentário