Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Alpha comparisons

Status
Not open for further replies.

kalona

Programmer
Joined
Jul 23, 2007
Messages
2
Location
US
I need help writing a Select statement in Oracle


Problem: Display the titles for all books whose titles are simply one word.

Select title
from books
where title =

This is as far as I can get.
Can someone help me please?

Visitor
 
If you want something specific to Oracle you might try one of the 15 oracle forums on Tek-Tips.

As for ANSI SQL you can issue....

Select title
from books
where title not like ( '% %' );


there is a space between the two % symbols.

The % and _ characters may be used in any combination in pattern_expression.

The % (PERCENT SIGN) character represents any string of zero or more arbitrary characters.

The _ (LOW LINE) character represents exactly one arbitrary character.



 
tdatgod was right, but not totally.
Correct i think it is this:

Select title
from books
where ltrim(rtrim(title)) not like ( '% %' );

Regards


web/sql developer
 
Thanks for your help, I will try it. I'm totally new
and thought there was a specific code for retrieving
'one word 'out of say three in the title.

 
Hi,
if you are looking for a particular word in tthe title like

Give me a list of books with Baseball in the title.

Select title
from books
where title like ( '%BaseBall%' );

if you mean something like

give me a list of books where the 3rd word in the title is Baseball....

anyone?
 
tdatgod look...

IN Oracle
first step:

create a function:

create or replace function wordcnt(sir varchar2) return number is
i number;
j number;
Result number;
begin
j:=0;
Result:=0;
i:=1;
while i<>0 loop
j:=j+1;
i:=instr(ltrim(rtrim(sir)),' ',1,j);
if i+1<>instr(ltrim(rtrim(sir)),' ',1,j+1) then Result:=Result+1;
end if;
end loop;
return(Result);
end wordcnt;


second step:

try something like this

select
'a tram named desire'
from dual
where wordcnt(substr('a tram named desire',1,instr('a tram named desire','desire')+length('desire')))=4

will have this output

a tram named desire

so...

in an abstract way is this:

select * from table
where wordcnt(substr(field,1,instr(field,searched_pattern)+length(searched_pattern)))=value_of_pozition_wanted


Are you satisfied?
web/sql developer
 

Hi,
Please see the ANSI SQL forum FAQ.

faq220-1073

If you want a vendor specific solution please refer the question to the Vendor specific Forum on Tek-Tips.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top