SCRIPT HELP!!!!!

[mkey]

hI ALL,
My data looks something like this:
column name: col_one varchar2(100)

#2-6",8" CRUDE PIPELINE
#5-10 Evaluate
NT Server
MYPRODUCT
#5-11 TANK Pipe
#9-3" PRODUCT CRUDE

What I want to output is these values:
#5-10 Evaluate
NT Server
#5-11 TANK Pipe


Now my script looks something like this:
select DESCRIPTN
from table_b
where (DESCRIPTN like upper(substr(descriptn,1,1))||lower(substr(descriptn,2,1))||'%'
or descriptn like lower(substr(descriptn,1,1))||upper(substr(descriptn,2,1))||'%'
or descriptn like lower(substr(descriptn,1,2))||'%')
and upper(descriptn)=upper(descriptn);


The output I'm getting is this:
#2-6",8" CRUDE PIPELINE
#5-10 Evaluate
#5-11 TANK Pipe
#9-3" PRODUCT CRUDE

What am I doing wrong hear?
Thank you guys!

 

[jee]

What are you really trying to do? It looks like, from the results you want, you are trying to eliminate any string that has all uppercase letters. You could do this with:

SELECT descriptn
FROM table_b
WHERE UPPER (descriptn) != descriptn;
[\code]

What the query you wrote will select is

1) All strings with first character upper case and second character lower case 
AND
2) all strings with first charter lower case and second character upper case
AND
3) all strings with first two characters lower case

Characters that are not alphabetic match both upper case and lower case. All of the strings in your table are selected by all three of the parts of your WHERE clause that are joined by ORs except the "NT Server" string and the "MYPRODUCT" string. These two are eliminated by all three parts.

Note the part of the WHERE clause that says
 
and upper(descriptn)=upper(descriptn);

eliminates no rows at all.  If is like saying Where 1=1 (which is always true). If this is removed your query returns the same results.

 

[mkey]

Stupid me ha. Thank you so much Gee!!!!!!!

 

[mkey]

Stupid me ha. Thank you so much Jee!!!!!!!