Sql query, find keywords in a string

[apnea]

I have a Field in my database that contains strings. (VARCHAR2)I like to build a search page which is working like a search engine (without any restrictions, capital or small letters etc.)What is the sql query for this purpose?
Thak you for your help

 

[carp]

Assuming you have a variable called :my_var which the user can fill in on your page, this might work:

SELECT whatever_columns_you_want
FROM my_table
WHERE INSTR(UPPER(text_column_name),UPPERmy_var)) <> 0;

You could probably use the LIKE operator, but this might take some playing around with.

 

[apnea]

Carp thank you for your answere, you qery is working fine without restrictions for capital or small leters.But i like to do one thing more e.g I have this string: &quot;On site technical assistance&quot; When i try to search something like:
&quot;on technical&quot; the result should be the string above.How can i build this query?
Thanks

 

[mohmehran]

This is not defined in the ANSI-SQL standard. You should stick to the services provided by your DBMS. For example SQL Server has a service named Full-Text Search that will do what you want. Even you will be able to search for different forms of verbs. Refer to your DBMS documentation.
Mohammad Mehran Nikoo, MCP
mohmehran@yahoo.com

 

[mohmehran]

But first try this:

SELECT *
FROM tblTable
WHERE UPPER(fldTest) LIKE '%ON%TECHNICAL%'

Mohammad Mehran Nikoo, MCP
mohmehran@yahoo.com

 

[MikeJones]

Assuming your on Oracle (Or can find a similar command for your own DBMS) if the user is going to type in 'on technical' and you want the row 'On site technical assistance' to return try the following.

select *
from (select 'On site technical assistance' a
from dual)
where upper(a) like '%'||
upper(translate('on technical',' ','%'))||'%'
/

This assumes that the you want to OR all the words typed in by the user. substitute (select 'On site technical assistance' a from dual) with your table and the a in the upper function to what ever column holds the full string you want to search. Oh and change the 'on technical' to the variable being passed in.

HTH,

Mike.