Ahhhh.....

I think I finally get it. Check my math on this one ssmith.

IF the number begins with '07769...', then you want to put that into a query and get out the prefix 1 - 00769.

However, IF the number begins with '07768...', then return prefix 4 - 0076

Finally, IF the number begins with '07792...', then return prefix 5 - 077

Am I right?

If so, I will further assume that, at some point, you will wish to do this across multiple numbers, which may be stored in another table (PhoneNum_T)

If so...

SELECT
---*
FROM
---(
---SELECT
------PN.PHONENUMBER,
------T.PREFIX_ID
---FROM
------TEST_T---------T,
------PHONENUMBER_T---PN,
---WHERE
------PN.PHONENUMBER---LIKE---T.PREFIX || '%'
---ORDER BY
------LENGTH(T.PREFIX) DESC
---)
WHERE
---ROWNUM = 1

This should get you what you want and allow the use of an index.

HTH,

- Chris