Identifying alpha characters only - help

djw_123m_at_hotmail.com (Lakeuk) wrote in message news:<bcdae9a0.0207310209.3db4eab2_at_posting.google.com>...
> I have a list of values in a column of which I only want my results
> set to contain the records that match 3 alpha characters
>
> Value
> -----
> AAA
> 001
> 002
> AB
> A
> BBB
> BBB
>
> Using the data set above I want my query to only identify the
> following records
>
> Result
> ------
> AAA
> BBB
> BBB
>
> Has anyone got any ideas of what SQL I need to write to get the
> required result, I know I need to use the length function to match the
> 3 characters but I don't know how to identify alpha character only
> (non numeric)
>
> I'm using Oracle / PLSQL, note the above example is a small set of
> data
>
> Any help appreciated
> Thanks
>
> Dave

one way to do it would be to normalize the data. Something along these lines

select data from table where length(data) = 3 and data not between '000' and '999' and replace(data, substr(data,1,1), '_') = '___';

                     ( I used '_' you could use what ever character

that will take your AAA or BBB and make them into ___ for easier testing. So ABA would end up being _B_ and not match the normalized ___. This is a case senesitive solution. You could wrap the data's in the replace() with upper and fix that. That should do it. If not here is the pl/sql I played with to come up with the where clause, since I don't have any data like such to test the select with .

declare
foo varchar2(3);

begin
foo := 'AAA';

if length(foo) = 3 and foo not between '000' and '999' then

	if replace(foo, substr(foo,1,1), '_') = '___' then
		dbms_output.put_line(foo||' passed the test');
	else
		dbms_output.put_line(foo||' faield the test');
	end if;

I did this with Oracle 8.16
Enjoy

Adam Received on Wed Jul 31 2002 - 19:41:20 CEST