Alpha comparisons

[kalona]

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

 

[tdatgod]

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.

 

[barbarul2001]

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

 

[kalona]

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.

 

[tdatgod]

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?

 

[barbarul2001]

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

 

[tdatgod]

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.