Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose
Operators manipulate individual data items called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication
operator is represented by an asterisk (*). Operators, in Oracle are implemented by means of creating in most cases overloaded objects, similar to PL/SQL packages that contain functions only.
The following SQL statement:
SELECT operator_name, number_of_binds
FROM dba_operators
ORDER BY 2;
will show you, for example, that the CONTAINS operator has 24 separate binds (overloads).
The Oracle Database supports the following case-insensitive collations: BINARY_CI, BINARY_AI, GENERIC_M_CI, GENERIC_M_AI, UCA0700_DUCET_CI, UCA0700_DUCET_AI
SELECT ename
FROM employee
ORDER BY 1 COLLATE GENERIC_M;
SELECT ename
FROM employee
ORDER BY 1 COLLATE BINARY_CI;
ALTER TABLE fuzzy
ADD CONSTRAINT pk_fuzzy
PRIMARY KEY(fid);
INSERT INTO fuzzy VALUES (1, 'The Quick Brown Fox', 'The quick brown fox');
INSERT INTO fuzzy VALUES (2, 'The Quick Brown Fox', 'The Quick Brown Fox');
INSERT INTO fuzzy VALUES (3, 'The quick grey wolf', 'The quick timber wolf');
INSERT INTO fuzzy VALUES (4, 'The glacially slow turtle', 'the glacially slow turtle');
INSERT INTO fuzzy VALUES (5, 'Expresso', 'Four score and seven years ago');
COMMIT;
SELECT * FROM fuzzy;
FID PHRASE1 PHRASE2
---- ------------------------- ------------------------
1 The Quick Brown Fox The quick brown fox
2 The Quick Brown Fox The Quick Brown Fox
3 The quick grey wolf The quick timber world
4 The glacially slow turtle the glacially slow turtle
5 Expresso
Four score and seven years ago
set linesize 201
col lcs format 999
col lev format 999
col umjws format 999
col umjw format 9D9999
SELECT phrase1, phrase2,
fuzzy_match(bigram, phrase1, phrase2) AS bg,
fuzzy_match(damerau_levenshtein, phrase1, phrase2) AS dlev,
fuzzy_match(jaro_winkler, phrase1, phrase2) AS jw, utl_match.jaro_winkler(phrase1, phrase2) AS umjw, utl_match.jaro_winkler_similarity(phrase1, phrase2) AS umjws,
fuzzy_match(levenshtein, phrase1, phrase2) AS lev,
fuzzy_match(longest_common_substring, phrase1, phrase2) AS lcs,
fuzzy_match(trigram, phrase1, phrase2) AS tri,
fuzzy_match(whole_word_match, phrase1, phrase2) AS wwm)
FROM fuzzy
ORDER BY fid;
PHRASE1 PHRASE2 BG DLEV JW UMJWUMJWS LEV LCS TRI WWM
-------------------------- ------------------------------ --- ---- --- ------- ----- --- --- --- ---
The Quick Brown Fox The quick brown fox 66 85 93 .9368 93 85 26 47 25
The Quick Brown Fox The Quick Brown Fox 100 100 100 1.0000 100 100 100 100 100
The quick grey wolf The quick timber wolf 65 77 92 .9291 92 77 47 57 75
The glacially slow turtle the glacially slow turtle 95 96 97 .9733 97 97 96 95 75
Expresso Four score and seven years ago 3 17 46 .4639 46 17 6 0 0
Fuzzy Match: Edit Tolerance
SELECT phrase1, phrase2,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 20) AS wwmet20,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 30) AS wwmet30,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 40) AS wwmet40,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 50) AS wwmet50,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 60) AS wwmet60,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 70) AS wwmet70,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 80) AS wwmet80,
fuzzy_match(whole_word_match, phrase1, phrase2, edit_tolerance 90) AS wwmet90
FROM fuzzy
ORDER BY fid;
PHRASE1 PHRASE2 W20 W30 W40 W50 W60 W70 W80 W90
------------------------- ------------------------------ --- --- --- --- --- --- --- ---
The Quick Brown Fox The quick brown fox 100 100 100 100 100 75 75 25
The Quick Brown Fox The Quick Brown Fox 100 100 100 100 100 100 100 100
The quick grey wolf The quick timber wolf 75 75 75 75 75
75 75 75
The glacially slow turtle the glacially slow turtle 100 100 100 100 100 75 75 75
Expresso Four score and seven years ago 17 0 0 0 0 0 0 0
Fuzzy Match: Relate To Shorter
SELECT phrase1, phrase2,
fuzzy_match(bigram, phrase1, phrase2, relate_to_shorter) AS bg,
fuzzy_match(damerau_levenshtein, phrase1, phrase2, relate_to_shorter) AS dlev,
fuzzy_match(jaro_winkler, phrase1, phrase2, relate_to_shorter) AS jw, utl_match.jaro_winkler(phrase1, phrase2) AS umjw, utl_match.jaro_winkler_similarity(phrase1, phrase2) AS umjws,
fuzzy_match(levenshtein, phrase1, phrase2,
relate_to_shorter) AS lev,
fuzzy_match(longest_common_substring, phrase1, phrase2, relate_to_shorter) AS lcs,
fuzzy_match(trigram, phrase1, phrase2,relate_to_shorter) AS tri,
fuzzy_match(whole_word_match, phrase1, phrase2, relate_to_shorter) AS wwd
FROM fuzzy
ORDER BY fid;
PHRASE1 PHRASE2 BG DLEV JW UMJWUMJWS LEV LCS TRI WWM
------------------------- ------------------------------ --- ---- --- ------ ----- --- --- --- ---
The Quick Brown Fox The quick brown fox 66 85 93 .9368 93 85 26 47 25
The Quick Brown Fox The Quick Brown Fox 100 100 100 1.0000 100 100 100 100 100
The quick grey wolf The quick timber wolf 72 74 92 .9291 92 74 52 64 75
The glacially slow turtle the glacially slow turtle 95 96 97 .9733 97 96 96 95 75
Expresso Four score and seven years ago 14 4294967084 46 .4639 46 ### 25 0 ###
Fuzzy Match: Unscaled
SELECT phrase1, phrase2,
fuzzy_match(bigram, phrase1, phrase2, unscaled) AS bg,
fuzzy_match(damerau_levenshtein, phrase1, phrase2, unscaled) AS dlev,
fuzzy_match(jaro_winkler, phrase1, phrase2, unscaled) AS jw, utl_match.jaro_winkler(phrase1, phrase2) AS umjw, utl_match.jaro_winkler_similarity(phrase1, phrase2) AS umjws,
fuzzy_match(levenshtein, phrase1, phrase2, unscaled) AS lev,
fuzzy_match(longest_common_substring, phrase1, phrase2, unscaled) AS lcs,
fuzzy_match(trigram, phrase1, phrase2,unscaled) AS tri,
fuzzy_match(whole_word_match, phrase1, phrase2, unscaled) AS wwm
FROM fuzzy
ORDER BY fid;
PHRASE1 PHRASE2 BG DLEV JW UMJWUMJWS LEV LCS TRI WWM
-------------------------- ------------------------------ --- ---- ----- ------ ----- --- --- --- ---
The Quick Brown Fox The quick brown fox 12 3 .93 .9368 93 3 5 8 1
The Quick Brown Fox The Quick Brown Fox 18 0 1.00 1.0000 100 0 19 17 4
The quick grey wolf The quick timber wolf 13 5 .92 .9291 92 5 10 11 3
The glacially slow turtle the glacially slow turtle 23 1 .97 .9733 97 1 24 22 3
Expresso Four score and seven years ago 1 25 .46 .4639 46 25 2 0 0
PHONIC_ENCODE takes the algorithm to be used as the first argument, the string to be processed as the second argument, and an optional max_code_len argument that controls the length of the desired output. max_code_len must be an integer between 1 and 12.
SELECT phrase1, phrase2,
phonic_encode(DOUBLE_METAPHONE, phrase1) c1,
phonic_encode(DOUBLE_METAPHONE_ALT, phrase2) c2,
phonic_encode(DOUBLE_METAPHONE_ALT, phrase2, 1) mcl1,
phonic_encode(DOUBLE_METAPHONE_ALT, phrase2, 4) mcl4,
phonic_encode(DOUBLE_METAPHONE_ALT, phrase2, 8) mcl8,
phonic_encode(DOUBLE_METAPHONE_ALT, phrase2, 12) mcl12
FROM fuzzy
ORDER BY fid;
PHRASE1 PHRASE2 C1 C2 MCL1 MCL4 MCL8 MCL12
-------------------------- ------------------------------- ---- ---- ----- ---- -------- -------------
The Quick Brown Fox The quick brown fox OKKP TKKP T TKKP TKKPRNFK TKKPRNFKS
The Quick Brown Fox The Quick Brown Fox OKKP TKKP T TKKP TKKPRNFK TKKPRNFKS
The quick grey wolf The quick timber wolf OKKK TKLX T TKKT TKKTMPRL TKKMPRLF
The glacially slow turtle the glacially slow turtle OKLS TKLX T TKLX TKLXLSLT TKLXLSLTRTL
Expresso Four score and seven years ago AKSP FRSK F FRSK FRSKRNTS FRSKRNTSFNRS
Data Quality operators and PL/SQL
DECLARE
outVal NUMBER;
BEGIN
outVal := fuzzy_match(jaro_winkler, 'A', 'B');
END;
/
*
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00201: identifier 'FUZZY_MATCH' must be declared
DECLARE
retVal NUMBER;
BEGIN
SELECT fuzzy_match(jaro_winkler, 'A', 'B') INTO retVal;
dbms_output.put_line(retVal);
END;
/
PL/SQL procedure completed successfully.
DECLARE
retVal dbms_id;
BEGIN
retVal := phonic_encode(double_metaphone, 'The quick brown fox');
END;
/
*
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00201: identifier 'PHONIC_ENCODE' must be declared
DECLARE
retVal dbms_id;
BEGIN
SELECT phonic_encode(double_metaphone, 'The quick brown fox') INTO retVal;
dbms_output.put_line(retVal);
END;
/
Note: Traditional pivoting is done with DECODE and CASE so you may want to look at those demos on the DECODE page
Pivot / Unpivot Demo 1
PIVOT [XML] (<aggregate function> (expression) [AS <alias>]
FOR (<column_list>)
IN <subquery>)
UNPIVOT [<INCLUDE | EXCLUDE> NULLS] (<column_list>) FOR (<column_list>)
IN (<column_list>) [AS (<constant_list>)])
conn oe/oe@pdbdev
CREATE TABLE pivot_table AS
SELECT * FROM (
SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total
FROM orders)
PIVOT (SUM(order_total)
FOR order_mode
IN ('direct' AS Store, 'online' AS Internet));
desc pivot_table
SELECT * FROM pivot_table;
SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
EXPLAIN PLAN FOR
SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
SELECT * FROM pivot_table
UNPIVOT INCLUDE NULLS (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
Pivot / Unpivot Demo 2
conn uwclass/uwclass@pdbdev
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));
EXPLAIN PLAN FOR
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));
CREATE TABLE pivot_table AS
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));
desc pivot_table
SELECT * FROM pivot_table;
SELECT * FROM pivot_table
UNPIVOT (sumx FOR AAL IN (AAL AS 'AAL', DAL AS 'DAL', ILC AS 'ILC', NWO AS 'NWO', SAL AS 'SAL', SWA AS 'SWA', USAF AS 'USAF'))
ORDER BY 2,1;
Unpivot with GROUP BY
conn scott/tiger@pdbdev
SELECT *
FROM (
SELECT ename, job, sal, comm
FROM emp)
UNPIVOT (income_component_value FOR income_component_type
IN (sal, comm))
ORDER BY 1;
SELECT *
FROM emp
WHERE ename = 'ALLEN';
SELECT ename, job, SUM(income_component_value) income
FROM (
SELECT ename, job, sal, comm
FROM emp)
UNPIVOT (income_component_value FOR income_component_type
IN (sal, comm))
GROUP BY ename, job
ORDER BY 1;
EXPLAIN PLAN FOR
SELECT ename, job, SUM(income_component_value) income
FROM (
SELECT ename, job, sal, comm
FROM emp)
UNPIVOT (income_component_value FOR income_component_type
IN (sal, comm))
GROUP BY ename, job
ORDER BY 1;
