CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Chopsticks
Chopsticks

Reputation: 37

Printing the longest substring in a list of letters

The task asks to print the longest substring in the alphabetical order. For example:

'kotafgovlav' -> longest alphabetical substring: 'afgov'

or if they are both equal long then the first one should be taken:

'abcbcd' -> longest alphabetical substring: 'abc'

Here is the what I've done so far: 

DECLARE 
x varchar2(12) := 'kotafgovlav';
ind varchar2(12);
BEGIN 
  for i in 1..length(x) loop
  if substr(x, i, 1) > substr(x, i-1, 1)
    end if; 
  end loop; 
END;

As you can see, I didn't get the result that i wanted (not even close). Can you maybe give me a hint/suggestions to solve this task? Thanks in advance.

Upvotes: 0

Views: 84

Answers (4)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

You can use this block.

SET SERVEROUTPUT ON;
DECLARE
  x         VARCHAR2(12) := 'kotafgovlav';
  l_substr  VARCHAR2(12) := SUBSTR(x,1,1);
  l_longest VARCHAR2(12) := SUBSTR(x,1,1);
BEGIN
  FOR i IN 2..length(x)
  LOOP
    IF ASCII( SUBSTR(x,i,1) ) >= ASCII( SUBSTR(x,i-1,1) ) THEN
      l_substr                := l_substr||SUBSTR(x,i,1);
    ELSE
      IF LENGTH(l_substr) >= LENGTH(l_longest) THEN
        l_longest         := l_substr;
      END IF;
      l_substr := SUBSTR(x,i,1);
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('longest substring '||l_longest);
END;

Upvotes: 2

Nikhil Shetkar
Nikhil Shetkar

Reputation: 346

Just to make you understand used dbms_output to verify. Below is the code :

   DECLARE 
    x varchar2(12) := 'pqrsabc1de';
    ind varchar2(12);
    counter number := 1;
    max_counter number := 0;
    str_cr varchar2(100);
    str varchar2(100);
    BEGIN 
    str_cr := str_cr || substr(x, 1, 1);
      for i in 1..length(x) loop
     -- dbms_output.put_line('one:'||substr(x, i, 1));
     -- dbms_output.put_line('two:'||substr(x, i+1, 1));
          if substr(x, i+1, 1) > substr(x, i, 1) then
             counter := counter + 1;
             str_cr := str_cr || substr(x, i+1, 1);
          -- dbms_output.put_line('str_cr:'||str_cr);
         --     dbms_output.put_line('counter:'||counter);
         else
          if max_counter < counter then
              max_counter := counter;
               str := str_cr;
               str_cr := substr(x, i+1, 1);
               counter := 1;
          else 
            str_cr := substr(x, i+1, 1);
            counter := 1;
          end if;
          -- dbms_output.put_line('str:'||str);
           -- exit;  -- exits current loop
        end if; 

      end loop; 
       dbms_output.put_line('str_final:'||str);
    END;

Upvotes: 1

MT0
MT0

Reputation: 168051

You can do it in a single SQL statement:

SELECT substring
FROM   (
  SELECT RANK() OVER ( ORDER BY LEVEL DESC ) As rnk,
         SUBSTR(
           str,
           CONNECT_BY_ROOT( idx ),
           idx - CONNECT_BY_ROOT( idx ) + 1
         ) AS substring
  FROM (
    SELECT LEVEL AS idx,
           str,
           SUBSTR( str, LEVEL, 1 ) As value
    FROM   ( SELECT 'kotafgovlav' AS str FROM DUAL )
    CONNECT BY LEVEL <= LENGTH( str )
  )
  WHERE CONNECT_BY_ISLEAF = 1
  CONNECT BY PRIOR value   < value
         AND PRIOR idx + 1 = idx
)
WHERE rnk = 1

Upvotes: 2

J. Chomel
J. Chomel

Reputation: 8395

For the first issue with your code, getting out of your loop with exit could be a solution;

DECLARE 
x varchar2(12) := 'kotafgovlav';
ind varchar2(12);
BEGIN 
  for i in 1..length(x) loop
    if substr(x, i, 1) > substr(x, i-1, 1) then
      dbms_output.put_line('i:'||i);  
      -- here do something because your test is ok
      exit;  -- exits current loop
    end if; 
  end loop; 
END;

... Then it's all algorithm. First you must learn PL/SQL.

Upvotes: 1

Related Questions