about search query

I would like to write query which can
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

1. ignore whether the search keyword is Upper case or lower Case

SQL Queries are case insensitive.

2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

No idea. I am unable to understand your question.
|||

sorry for my unclear idea!
What I want to do:
If people input the word in (a), the ColA contain word (b) will also be selected.
For example, select count(*) from tableA where ColA like '%compare%' , or sth like that

(a) (b)
1.CASW Casw

2.compare comparing

1. Since the keyword is in '', the case is sensitive, so Casw should not be select
2. Similary, since search compare will include provide result which have comparing,
Thx

|||I am sorry. I am still unable to understand your requirement. Can you try and xplain a little more?|||Thx for your patient!
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?


|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||

ndinakar wrote:

you should get both records without having to worry about case.

Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||

DECLARE @.tTABLE (colAvarchar(10), colBvarchar(100))

INSERTINTO @.tVALUES ('Tom','FOOTBALL,PIANO')

INSERTINTO @.tVALUES ('Sam','football,piano,volleyball')

SELECT*FROM @.tWHERE colBLIKE'%football%'
-------------

(1 row(s) affected)

(1 row(s) affected)

colA colB

---- ------------------------------

Tom FOOTBALL,PIANO

Sam football,piano,volleyball

(2 row(s) affected)

|||Thanks for the followup ndinkar. I always that my query didnt worked.