Get common prefix

prefixes.sql

DECLARE
  vch VARCHAR2(30);
  
  FUNCTION GET_COMMON_PREFIX(
    vch_VC_START                      IN VARCHAR2,
    vch_VC_END                        IN VARCHAR2)
  RETURN VARCHAR2
  IS
    num_Length                        NUMBER;
    vch_Start                         VARCHAR2(30);
    vch_End                           VARCHAR2(30);
    --
    vch_Prefix                        VARCHAR2(30);
  BEGIN
    num_Length := LENGTH(vch_VC_START);
    
    FOR i IN 1..num_Length
    LOOP
      vch_Start := SUBSTR(vch_VC_START, 1, num_Length - i + 1);
      vch_End   := SUBSTR(vch_VC_END,   1, num_Length - i + 1);
      
      dbms_output.put_line('start : ' || vch_Start || ', end: ' || vch_End);
      
      IF vch_Start = vch_End THEN
        vch_Prefix := vch_Start;
        EXIT;
      END IF;
    END LOOP;
    
    RETURN vch_Prefix;
  END GET_COMMON_PREFIX;
BEGIN
  vch := GET_COMMON_PREFIX('12332', '78733');
  
  dbms_output.put_line('common prefix is ' || vch);
END;