Security Software

Hi,

Hopefully this will solve your problem

vineet@ORCL>desc temp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(30)

vineet@ORCL>select * from temp;

A
------------------------------
5500
554
5540
5539

Elapsed: 00:00:00.04
vineet@ORCL>l
  1  select * from
  2  (
  3  select * from temp
  4  where a in (
  5             select substr(dual_q.input, 1 , in_list.rn )
  6             from (
  7                       select input , length(input) ln
  8                             from (select '&input' input from dual)
  9                  ) dual_q,
 10                     (
 11                             select row_number() over (order by 1) rn from dual
 12                             group by cube(1, 2, 3,4, 5)
 13                     ) in_list
 14             where  dual_q.ln >= in_list.rn
 15     )
 16  order by 1 desc
 17  )
 18* where rownum < 2
vineet@ORCL>/
Enter value for input: 5544
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '5544' input from dual)

A
------------------------------
554

Elapsed: 00:00:00.04
vineet@ORCL>/
Enter value for input: 55397
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '55397' input from dual)

A
------------------------------
5539

Elapsed: 00:00:00.03
vineet@ORCL>/
Enter value for input: 55409
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '55409' input from dual)

A
------------------------------
5540

Elapsed: 00:00:00.03
vineet@ORCL>

Vineet

SELECT MAX (column_a)
	   KEEP (DENSE_RANK FIRST ORDER BY LENGTH(column_a) DESC) magic_4u
  FROM t
 WHERE column_a IN (SELECT     SUBSTR (str, 1, LEVEL)
                          FROM (SELECT :input str
                                  FROM DUAL)
                    CONNECT BY LEVEL <= LENGTH (str))

SQL> create table t as select '5500' a from dual
 union select '554' from dual 
 union select '5540' from dual 
 union select '5539' from dual;

Table created.

SQL> select max(a) a from t where '&input' like a||'%';
Enter value for input: 5544
A
----
554