Compare subparts of a field over multiple rows

[KiltedScot]

OK, I'm fairly new to PL/SQL, adn I have a query to write, which is confusing me. Let me explain:

I have a table, with three fields, CustID, CustName, Number

What I want to do, is identify and display all rows in the table, where the last four digits of the Number field match. So, for example, if teh table contained the following:

CustID CustName Number
-------- ----------- ---------
1 Brown 12345678
2 Smith 23456789
3 Jones 23445678
4 Davies 90908989
5 Royle 56231490

I would want the query to display:

CustID CustName Number
-------- ----------- ---------
1 Brown 12345678
3 Jones 23445678

As I say, I'm fairly new to PL/SQL, and so I'm hoping that someone will be able to explain it very simply to me.

Many thanks

KiltedScot

[Nige Ridd]

That can be solved using SQL, which is always preferable to use rather than PL/SQL if you can. The following would give you the results, there may be better solutions but as it's not too horrendous I thought it should be OK.

select custid, custname, custnumber
from testtable
where mod(custnumber, 10000) in ( select mod(custnumber, 10000 )
from testtable
group by mod(custnumber, 10000 )
having count ( mod(custnumber, 10000 ) ) > 1 ) ;

You may have to change some of the column or table names, but hopefully it's OK.

Nigel

[debasisdas]

For this no need of writing any PL / SQL at all . simmply try to use like search . try the sample code

select * from customer where cust_number like '%5678'

Again are you sure the field name is number ? It is a reserved key word . Try to avoid that as a field name.