search text

[deddleston]

i have a jsp webpage (search engine) when searching for text e.g;

CUST i need the results to include -

CUST
Cust
cust

do you know what i mean... any help much appreciated.

M$ arn't the only option!

 

[BJCooperIT]

Have you tried using a function like "upper" with "like"?

Example:

upper(TEXT_STRING) like '%CUST%'

Hope this helps!

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[deddleston]

well no - because then it will just convert whatever is searched for into uppercase, and only uppercase rresults will be shown. i need upper,lower and a mix (if they exist)

M$ arn't the only option!

 

[BJCooperIT]

Not exactly, see below:

SQL> create table test_it (text varchar2(30));

Table created.

SQL> insert into test_it values ('xxxcustxxx');

1 row created.

SQL> insert into test_it values ('xxxx zzzz Cust');

1 row created.

SQL> insert into test_it values ('CUST sssssssss');

1 row created.

SQL> select * from test_it
  2  where upper(text) like '%CUST%';

TEXT
------------------------------
xxxcustxxx
xxxx zzzz Cust
CUST sssssssss

SQL>

Is this not the result you wanted?

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[rcurva]

Or better yet make use of SOUNDEX function for fuzzy search.

Example
SELECT ename
FROM emp
WHERE SOUNDEX(ename)
= SOUNDEX('SMYTHE');

ENAME
----------
SMITH



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War