2018 Oracle SQL Puzzle of the Week #4

Calculate Mutual Funds’ Performance

For a given table fund_performance (see the CREATE TABLE statement below), calculate each fund’s performance over the 6-month period from Jan-2016 till Jun-2016.

  • Use a single SELECT statement
  • Performance is calculated as a multiplication of all the months’ performance rates for the given time frame
  • The solution should work for any time frame, so treat from-month and to-month as query parameters
  • DDL command:
CREATE TABLE fund_performance AS
SELECT 1 fund_id, '2016-01' perf_month, 1.05 perf_rate
FROM dual 
UNION ALL
SELECT 1, '2016-02', 1.02 FROM dual UNION ALL
SELECT 1, '2016-03', 0.92 FROM dual UNION ALL
SELECT 1, '2016-04', 1.01 FROM dual UNION ALL
SELECT 1, '2016-05', 1.04 FROM dual UNION ALL
SELECT 1, '2016-06', 0.95 FROM dual UNION ALL
SELECT 2, '2016-01', 1.04 FROM dual UNION ALL;
SELECT 2, '2016-02', 1.03 FROM dual UNION ALL
SELECT 2, '2016-03', 0.98 FROM dual UNION ALL
SELECT 2, '2016-04', 1.04 FROM dual UNION ALL
SELECT 2, '2016-05', 1.01 FROM dual UNION ALL
SELECT 2, '2016-06', 0.98 FROM dual;

Expected Result:

FUND_ID Cumulative Performance
1 0.98
2 1.08

A correct answer (and workarounds!) will be published here in about a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

3 Solutions to 2018 Oracle SQL Puzzle of the Week #3

2018 Puzzle of the Week #3:

Exact Coin Change Puzzle.

Suppose that you are a sales person at a cash register and you have one purchase to serve before you close. A buyer has to pay X dollars and N cents with bills only (no coins). You have lots of bills of various nomination and limited number of coins: 3 quarters, 9 dimes, 19 nickels, and 4 pennies left in the register. You are required to give the exact change (between 1 and 99 cents) using smallest number of (available) coins.

  • Use a single SELECT statement
  • The result should return 1 row and 4 columns indicating how many coins of each type to use
  • 1 Quarter = 25 cents; 1 Dime = 10 cents; 1 Nickel = 5 cents

Sample result for a change of 63 cents:

 
  Quarters      Dimes    Nickels    Pennies
---------- ---------- ---------- ----------
         2          1          0          3

Solutions:

Solution #1: Using Math formula and MODEL clause:

For American coins one can rely on a mathematical formula to get the smallest number of coins for exact change:

Quarters: FLOOR of [Change Amount]/25
Dimes: FLOOR(([Change Amount] – 25*[Quarters])/10)
Nickels: FLOOR(([Change Amount] – 25*[Quarters]-10*[Dimes])/5)
Pennies: [Change Amount] – 25*[Quarters]-10*[Dimes] – 5*[Nickels]

One of the easiest ways to implement this strategy is to employ the MODEL clause:

WITH m AS (
SELECT 63 AS cents
FROM dual 
)
SELECT cents "Change", 
       Q "Quarters", 
       D "Dimes", 
       N "Nickels", 
       P "Pennies"
FROM m
MODEL
DIMENSION BY(0 AS dummy)
MEASURES(
 cents,
 CAST(0 AS NUMBER(3)) AS Q,
 CAST(0 AS NUMBER(3)) AS D,
 CAST(0 AS NUMBER(3)) AS N,
 CAST(0 AS NUMBER(3)) AS P
)
RULES (
 Q[0]=FLOOR(CENTS[0]/25),
 D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
 N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
 P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)

Result:

Change Quarters Dimes Nickels Pennies
63 2 1 0 3

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

WITH m AS (
SELECT LEVEL cents
FROM dual 
CONNECT BY LEVEL<=99
)
SELECT cents "Change", 
       Q "Quarters", 
       D "Dimes", 
       N "Nickels", 
       P "Pennies"
FROM m
MODEL
PARTITION BY(ROWNUM AS rn)
DIMENSION BY(0 AS dummy)
MEASURES(
 cents,
 CAST(0 AS NUMBER(3)) AS Q,
 CAST(0 AS NUMBER(3)) AS D,
 CAST(0 AS NUMBER(3)) AS N,
 CAST(0 AS NUMBER(3)) AS P
)
RULES (
 Q[0]=FLOOR(CENTS[0]/25),
 D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
 N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
 P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)
ORDER BY 1

Result:

Change Quarters Dimes Nickels Pennies
1 0 0 0 1
2 0 0 0 2
3 0 0 0 3
4 0 0 0 4
5 0 0 1 0
6 0 0 1 1
7 0 0 1 2
8 0 0 1 3
9 0 0 1 4
10 0 1 0 0
95 3 2 0 0
96 3 2 0 1
97 3 2 0 2
98 3 2 0 3
99 3 2 0 4

Solution #2: Using Enhanced Math formula:

It’s easy to see that the MOD function is very handy in determining the number of coins other than quarters (the largest):

WITH a AS (
SELECT 63 cents
FROM dual
)
SELECT a.cents "Change",
       FLOOR(a.cents/25) "Quarters", 
       FLOOR(MOD(a.cents,25)/10) "Dimes",
       FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
       MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a

Alternatively, we can see coin combinations for all change amounts from 1 to 99 cents:

WITH a AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<100
)
SELECT a.cents "Change",
       FLOOR(a.cents/25) "Quarters", 
       FLOOR(MOD(a.cents,25)/10) "Dimes",
       FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
       MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a
ORDER BY a.cents

Solution #3: Using Cartesian Product and Top Record pattern approach:

If we did not know the exact math formula, we could still count on the brute force approach – go over all possible coin permutations (Cartesian product) that sum up to the required total amount and then chose the combination with the fewest number of coins (top record pattern):

WITH r AS (
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=20
), x AS (
SELECT q.n "Quarters", d.n "Dimes", n.n "Nickels", p.n "Pennies",
 RANK() OVER(ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p
WHERE q.n<=3
 AND d.n<=9
 AND n.n<=19 --not needed
 AND p.n<=4
 AND q.n*25 + d.n*10 + n.n*5 + p.n = 63 --amount of change
)
SELECT "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

WITH r AS ( -- this range is to be reused 5 times in this query
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=100
), x AS (
SELECT c.n "Change", q.n "Quarters", d.n "Dimes", 
       n.n "Nickels", p.n "Pennies",
       RANK() OVER(PARTITION BY c.n ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p, r c
WHERE q.n<=3
 AND d.n<=9
 AND n.n<=19 --now it is needed
 AND p.n<=4  AND q.n*25 + d.n*10 + n.n*5 + p.n = c.n --amount of change  
 AND c.n>0
)
SELECT "Change", "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1
ORDER BY 1

You can execute the above SQL statements in Oracle Live SQL environment.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

2018 Oracle SQL Puzzle of the Week #3

Exact Coin Change Puzzle.

Suppose that you are a sales person at a cash register and you have one purchase to serve before you close. A buyer has to pay X dollars and N cents with bills only (no coins). You have lots of bills of various nomination and limited number of coins: 3 quarters, 9 dimes, 19 nickels, and 4 pennies left in the register. You are required to give the exact change (between 1 and 99 cents) using smallest number of (available) coins.

  • Use a single SELECT statement
  • The result should return 1 row and 4 columns indicating how many coins of each type to use
  • 1 Quarter = 25 cents; 1 Dime = 10 cents; 1 Nickel = 5 cents

Sample result for a change of 63 cents:

 
  Quarters      Dimes    Nickels    Pennies
---------- ---------- ---------- ----------
         2          1          0          3

A correct answer (and workarounds!) will be published here in a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

3 Solutions to 2018 Oracle SQL Puzzle of the Week #2

2018 Puzzle of the Week #2:

For each of the following salary ranges select two randomly chosen employees:
0-999
1000-1999
2000-2999
3000+

Expected Result (in SQL*Plus):

ENAME      SAL        RANGE
---------- ---------- ---------
SCOTT            3000 3000+
FORD             3000 3000+
BLAKE            2850 2000-2999
CLARK            2450 2000-2999
TURNER           1500 1000-1999
MILLER           1300 1000-1999
JAMES             950 0-999
SMITH             800 0-999
  • Remember to use only a single SELECT statement.
  • Use table emp (from Oracle scott schema)

Solutions:

Solution #1: Using ROW_NUMBER with random.value functions:

We are applying a random sorting order to each of the salary ranges and take 2 top records from each range:

WITH x AS (
SELECT CASE WHEN sal<=999  THEN '0-999'
            WHEN sal<=1999 THEN '1000-1999'
            WHEN sal<=2999 THEN '2000-2999'
            ELSE                '3000+'
       END range,
       ename, sal
FROM emp
), y AS (
SELECT ename, sal, range, 
       ROW_NUMBER()OVER(PARTITION BY range 
                        ORDER BY dbms_random.value) rn
FROM x
)
SELECT range, ename, sal
FROM y
WHERE rn<=2
ORDER BY range

Result:

RANGE     ENAME      SAL
--------- ---------- ----------
0-999     JAMES      950
0-999     SMITH      800
1000-1999 WARD 1250
1000-1999 TURNER 1500
2000-2999 JONES 2975
2000-2999 CLARK 2450
3000+     FORD 3000
3000+     KING 5000

Result (of subsequent execution):

RANGE     ENAME             SAL
--------- ---------- ----------
0-999     SMITH             800
0-999     JAMES             950
1000-1999 WARD             1250
1000-1999 MARTIN           1250
2000-2999 BLAKE            2850
2000-2999 JONES            2975
3000+     SCOTT            3000
3000+     KING             5000

Solution #2: Using DECODE, MAX() KEEP and UNION ALL:

Instead of taking top 2 records (randomly sorted), we are taking top 1 and bottom 1 and combine them together. DECODE function mimics the CASE from the previous solution.

WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
                 SIGN(2999-sal), '2000-2999', '3000+') range,
       ename, sal, ROWNUM || dbms_random.value rnd
FROM scott.emp
)
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename,
              MAX(sal)  KEEP(DENSE_RANK FIRST ORDER BY rnd) sal
FROM x
GROUP BY range
UNION ALL
SELECT range, MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename,
              MAX(sal)  KEEP(DENSE_RANK LAST ORDER BY rnd) sal
FROM x
GROUP BY range
ORDER BY range

Result:

RANGE     ENAME             SAL
--------- ---------- ----------
0-999     JAMES             950
0-999     SMITH             800
1000-1999 MARTIN           1250
1000-1999 WARD             1250
2000-2999 JONES            2975
2000-2999 BLAKE            2850
3000+     FORD             3000
3000+     KING             5000

Note, that we concatenated ROWNUM with dbms_random.value to produce UNIQUE random value. Without ROWNUM (or any other KEY) there is always a chance that dbms_random.value will repeat on different rows and hence top and bottom values could be mixed and the same employee will be repeated twice.

Solution #3: Using SIN for random value simulation and multi-column UNPIVOT with MAX() KEEP function:

Instead of combining top and bottom records from two statements using UNION ALL, here were calculating top and bottom values as 1 record and UNPIVOT them to produce two rows per salary range:

WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
                 SIGN(2999-sal), '2000-2999', '3000+') range,
       ename, sal, 
       SIN(ROWNUM*TO_NUMBER(SUBSTR(
                             extract(second 
                                     from current_timestamp),-3))
           ) rnd
FROM scott.emp
), y AS (
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename1,
              MAX(sal)  KEEP(DENSE_RANK FIRST ORDER BY rnd) sal1,
              MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename2,
              MAX(sal)  KEEP(DENSE_RANK LAST ORDER BY rnd) sal2
FROM x
GROUP BY range
)
SELECT range, ename, sal
FROM y
UNPIVOT (
  (ename, sal) for (t1, t2) in ((ename1,sal1), (ename2,sal2))
)
ORDER BY range

Result:

RANGE     ENAME             SAL
--------- ---------- ----------
0-999     SMITH             800
0-999     JAMES             950
1000-1999 MILLER           1300
1000-1999 MARTIN           1250
2000-2999 CLARK            2450
2000-2999 BLAKE            2850
3000+     FORD             3000
3000+     SCOTT            3000

Note the use of multi-column UNPIVOT. Randomization simulation is based on a fairly random selection of the last 3 digits in the current timestamp’s second value. This number is used as a “seed”. When this seed is multiplied by the rownum, the result is used as a SIN function argument which makes the outcome pseudo-random.

You can execute the above SQL statements in Oracle Live SQL environment.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

 

2018 Oracle SQL Puzzle of the Week #2

Random Selection per Employee Salary Range

For each of the following salary ranges select two randomly chosen employees:
0-999
1000-1999
2000-2999
3000+

Expected Result (in SQL*Plus):

ENAME      SAL        RANGE
---------- ---------- ---------
SCOTT            3000 3000+
FORD             3000 3000+
BLAKE            2850 2000-2999
CLARK            2450 2000-2999
TURNER           1500 1000-1999
MILLER           1300 1000-1999
JAMES             950 0-999
SMITH             800 0-999
  • Remember to use only a single SELECT statement.
  • Use table emp (from Oracle scott schema)
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
  • The scoring rules can be found here.

A correct answer (and workarounds!) will be published here in a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

5 Solutions to 2018 Oracle SQL Puzzle of the Week #1

2018 Puzzle of the Week #1:

For a given text string, find the first (from the beginning) longest sub-string that does not have repeating characters.

Solutions:

Solution #1: Using CONNECT BY clause (for range generation), REGEXP_COUNT, and RANK() functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
), x AS ( 
SELECT SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1) substr, 
       RANK() OVER(ORDER BY r2.rn - r1.rn DESC, r1.rn) rk 
FROM r r1, r r2, w 
WHERE r1.rn<=r2.rn 
 AND REGEXP_COUNT(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1') = 0 
) 
SELECT substr 
FROM x 
WHERE rk=1

Result of execution in Oracle Live SQL client:

SUBSTR
rkans

Solution #2: Using CONNECT BY clause (for range generation), REGEXP_LIKE, and MAX() KEEP functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
) 
SELECT MAX(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1)) 
 KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr 
FROM r r1, r r2, w 
WHERE r1.rn<=r2.rn 
 AND NOT REGEXP_LIKE(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1')

Solution #3: Using CONNECT BY clause (twice), LATERAL view, REGEXP_COUNT, and RANK() functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), s AS ( 
SELECT SUBSTR(word, LEVEL) word, LEVEL rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
) 
SELECT MAX(x.substr) 
       KEEP(DENSE_RANK FIRST ORDER BY LENGTH(x.substr) DESC, s.rn) substr 
FROM s, LATERAL(SELECT SUBSTR(s.word, 1, LEVEL) substr 
                FROM dual 
                CONNECT BY LEVEL<=LENGTH(s.word)) x 
WHERE REGEXP_COUNT(x.substr, '(.).*\1') = 0

Solution #4: Using XMLTable function (for range generation), Correlated subquery with COUNT(DISTINCT), and MAX() KEEP function:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn, word
FROM w, XMLTABLE('for $i in 1 to $N cast as xs:integer return $i' 
                 PASSING LENGTH(w.word) AS N) x
) 
SELECT MAX(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1))
 KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr 
FROM r r1, r r2
WHERE r1.rn<=r2.rn 
 AND r2.rn - r1.rn + 1 = 
 (SELECT COUNT(DISTINCT SUBSTR(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1), 
                               LEVEL, 1)) 
 FROM dual 
 CONNECT BY LEVEL<=r2.rn - r1.rn + 1 
 )

Solution #5: Using CONNECT BY, Recursive CTE, INSTR, SUBSTR, and MAX() KEEP functions:

WITH w AS (
 SELECT 'arkansas' word
 FROM dual
), s(sub, word, lvl, rn) AS (
SELECT SUBSTR(word, LEVEL, 1), SUBSTR(word, LEVEL) word, 1, ROWNUM
FROM w
CONNECT BY SUBSTR(word, LEVEL) IS NOT NULL
UNION ALL
SELECT SUBSTR(word, 1, lvl+1), word, lvl+1, ROWNUM
FROM s
WHERE LENGTH(SUBSTR(word, 1, lvl+1))=lvl+1
 AND INSTR(sub, SUBSTR(word, lvl+1, 1))=0
)
SELECT MAX(sub) KEEP (DENSE_RANK FIRST ORDER BY lvl DESC, rn) substr
FROM s

You can execute the above SQL statements in Oracle Live SQL environment.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

 

 

 

 

 

 

2018 Oracle SQL Puzzle of the Week #1

This puzzle is sometimes offered at non-SQL technical interviews with companies like Google or Amazon:

For a given text string, find the first (from the beginning) longest sub-string that does not have repeating characters.

For example, the longest sub-string with no repeating characters in the word anaconda is acond; and in the word: stereotype – it is reotyp.

We move the bar higher and will solve this quite challenging problem in Oracle SQL:

  • Remember to use only a single SELECT statement.
  • The character case should be ignored (use or convert the value to a lower case)
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
  • The scoring rules can be found here.

If this puzzle is a bit tricky for you, start with a simpler one:

For a given text string, list all possible sub-strings.

If you are ready to give up, please don’t – try to come up with a PL/SQL solution (at very least). It could be a function that takes a VARCHAR2 argument and returns a VARCHAR2 result value.

A correct answer (and workarounds!) will be published here in a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

2018 Oracle SQL Puzzle of the Week Challenge!

Happy New Year to all Oracle fans! Our weekly SQL puzzle challenge is back. For those who missed our 2016 challenge, here is the link.

The rules will slightly change but mostly remain the same:

  • To submit your answer, please like this post, start following the blog and provide either a working SQL statement or (preferred!) a link to a script @ https://livesql.oracle.com (you will need to create a [free] Oracle account if you have not done so), so by clicking on your link I could quickly verify your solution.
  • The scoring rules:
    • The first person who submits a correct solution is granted 5 points
    • The second – 3 points
    • The third – 2 points
    • All others – 1 point
    • No points are given for solutions that duplicate previously posted ones.
    • Alternative solutions are more than welcomed. Each (correct) one will earn you one more point. So even a person who did not post the very first solution can earn most points by submitting more than 1 alternative solutions.
  • All puzzles will assume a solution that is based on a single SQL (SELECT) statement
  • The participation is FREE and there will be some prizes to the winners, so you have nothing to lose but to learn!
  • The weekly standing will be posted in my Oracle Facebook group, you are welcome to join the club!
  • The first phase of the challenge will consist of 15 puzzles and will last approximately 4 months.

Three Strategies for replacing multiple spaces with single ones.

Sometimes, we get to work with a text that has multiple sequential space characters that we don’t need, and hence need to remove. There are many ways to accomplish the task in pure Oracle SQL. Below, you can find 3 of them that I believe are worth mentioning.

We will be removing undesired spaces from the following character string:

Three   Strategies    for    replacing    multiple         spaces

Strategy #1: Regular Expressions

Of course, every time we need to process some text data, Regular Expressions come to mind. Here, the choice is very natural and straightforward:

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REGEXP_REPLACE(text, '[ ]+',' ') text
FROM x

Result:

TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces

Regular expression [ ]+    finds all occurrences of sequential spaces and regexp_replace function substitutes each of such occurrences with a single space.

Strategy #2: Triple Replace

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text
FROM x

Result:

TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces

To see how this trick works, let’s break it down in 3 steps:

Step 1: Replace Spaces with Pipe-Space combination:

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(text, ' ', '| ') text
FROM x

Result:

Three| | | Strategies| | | | for| | | | replacing| | | | multiple| | | | | | | | | spaces

Step 2: Remove all Space-Pipe combinations

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(REPLACE(text, ' ', '| '), ' |') text
FROM x

Result:

TEXT
---------------------------------------------------
Three| Strategies| for| replacing| multiple| spaces

Note, that we first replaced each space with PIPE-SPACE sequence and then removed the opposite order sequence SPACE-PIPE, which left us with non-repeated occurrences of PIPE-Space combinations.

Step 3: Remove PIPE characters

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text
FROM x

Result:

TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces

Strategy #3: Recursive WITH Clause

WITH x AS ( 
SELECT 'Three Strategies for replacing multiple spaces' AS text 
FROM dual 
), y(text) AS (
SELECT REPLACE(x.text, '  ', ' ')
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' ')
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0

Result:

TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces

The idea is to replace two-space combination with a single space until no more two-space sequence will be left in the text.

Final thoughts

What will change if we need to process not a single text value but a table/collection?

Let’s examine how each of the above strategies will work:

Strategy #1: Regular Expressions

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
)
SELECT REGEXP_REPLACE(text, '[ ]+',' ') text
FROM x

Result:

TEXT
-----------------------------------------------
Three Strategies for replacing multiple spaces
One more string with spaces

Strategy #2: Triple Replace

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
)
SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text
FROM x

Result:

TEXT
-----------------------------------------------
Three Strategies for replacing multiple spaces
One more string with spaces

So far, so good.

Strategy #3: Recursive WITH Clause

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
), y(text) AS (
SELECT REPLACE(x.text, '  ', ' ')
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' ')
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0

Result:

TEXT
-----------------------------------------------
One more string with spaces
Three Strategies for replacing multiple spaces

All is good except for the order – the line with the smaller number of “double” spaces came first as it was first cleaned up. To preserve the original sort order we will need to make a change:

WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
), y(text, rn) AS (
SELECT REPLACE(x.text, '  ', ' '), ROWNUM 
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' '), y.rn
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0
ORDER BY rn

Result:

TEXT
-----------------------------------------------
Three Strategies for replacing multiple spaces
One more string with spaces

Now, it’s all good!

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

TABLE functions in Oracle 12.2

Oracle 12.2 introduced a nice little feature – when selecting from a table-function you no longer have to use the TABLE function:

Before 12.2, here is what you had to do:

SELECT *
FROM TABLE([your-collection-or-table-function])

Starting with 12.2 the code becomes shorter:

SELECT *
FROM [your-collection-or-table-function]

Example (Oracle 12.2):

SELECT *
FROM sys.odcinumberlist(5,2,6,3,78)

Result:

COLUMN_VALUE
------------
           5
           2
           6
           3
          78

Note that if you try the last query in Oracle 12.1 or any prior release, you will get the following error:

FROM sys.odcinumberlist(5,2,6,3,78)
                       *
ERROR at line 2:
ORA-00933: SQL command not properly ended

This is an indication that you may need to check your Oracle version:

SELECT *
FROM v$version
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.