CODEWITHSUNDEEP
sqlstringoracle-databasemaxrecursive-query
determinator
determinator

Reputation: 139

Oracle SQL Find max digit in number in cell

I have a column like that :

enter image description here

How can I find the max digit column? ( I want to select the max digit from the left column and write it to a new column)

WITH ORACLE SQL

THANKS!!!

Upvotes: 0

Views: 384

Answers (5)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

but you can do it much easier and shorter: without any subqueries, CTE and connect-by.

just using translate, length and rtrim:

length(
   rtrim(
      translate(
        translate('0123456789',your_number,'          ')
       ,' 0123456789'
       ,'x          '
       )))-1 as max_number

Step-by-step description:

  1. translate('0123456789',x,' ') removes from '0123456789' existing digits. Lets call it remove_existing
  2. translate(remove_existing, ,' 0123456789','x ') replaces spaces in remove_existing with 'x'. So you by position of X which digits exist in your number, for example 'x x x x xx' means that you have '0 2 4 6 89' in your number. Let's call it existing_as_x
  3. length(rtrim(existing_as_x))-1 removes spaces from right and returns length, so returned length-1 is equal to your required maximum digit.

Full test case:

with t(x) as (
select * from table(sys.odcinumberlist(
134425,
6453356,
65267,
9999,
467533
))
)
select 
x,
translate('0123456789',x,'          ') remove_existing,
translate(
    translate('0123456789',x,'          ')
   ,' 0123456789','x          '
   ) as existing_as_x,
length(
   rtrim(
      translate(
        translate('0123456789',x,'          ')
       ,' 0123456789'
       ,'x          '
       )))-1 as max_number
from t;

Results:

         X REMOVE_EXISTING  EXISTING_AS_X MAX_NUMBER
---------- ---------------- ------------- ----------
    134425 0     6789        xxxxx                 5
   6453356 012    789          xxxx                6
     65267 01 34   89         x  xxx               7
      9999 012345678                 x             9
    467533 012     89          xxxxx               7

Upvotes: 0

Ranagal
Ranagal

Reputation: 317

Solving it like a pro:

Creating sample data:

CREATE TABLE tab as
WITH t(col) AS
(
 SELECT 134425  FROM dual UNION ALL
 SELECT 6453356 FROM dual UNION ALL
 SELECT 65267   FROM dual UNION ALL
 SELECT 9999    FROM dual UNION ALL
 SELECT 467533  FROM dual
)
SELECT * FROM t;

Solution:

select * from tab,
lateral
( 
    select max(substr(col, level, 1)) max_digit
    from   dual
    connect by level <= length(col)
)

Output:

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use a brute force method as well:

select (case when column like '%9%' then 9
             when column like '%8%' then 8
             . . . 
        end)

I mention this because a recursive query is not necessarily needed for this.

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

One option would be using Hierarchical query along with REGEXP_SUBSTR() function in order to split each digits individually first, and then find the maximum digit values for each grouped column values :

SELECT col, MAX( REGEXP_SUBSTR(col,'[^\d]',level) ) AS maxdigit
  FROM t
 CONNECT BY level <= LENGTH(col)
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR col = col
 GROUP BY col

Demo

Upvotes: 0

GMB
GMB

Reputation: 222432

One method uses a recursive query to split the number into digits. A recursive query comes handy for this:

with cte (col, val, pos, maxpos) as (
    select col, substr(col, 1, 1), 1, length(col) from mytable
    union all
    select col, substr(col, pos + 1, 1), pos + 1, maxpos from cte where pos < maxpos
)
select col, max(val) max_digit
from cte 
group by col

Demo on DB Fiddle:

    COL | MAX_DIGIT
------: | :--------
6453356 | 6        
   9999 | 9        
  65267 | 7        
 467533 | 7        
 134425 | 5

Upvotes: 0

Related Questions