//
you're reading...
Database, IT-Center, Oracle

String Functions in Oracle / PLSQL

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
ConcatenateOverload 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 FROMuser_tables;SELECT table_name, DUMP(table_name, 16) DMP FROMuser_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 BYname;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 namecol, SOUNDEX(namecol) FROM test;
— Thanks Frank van Bortel for the idea for the aboveSELECT *
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;

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: