ignoring case in select statement

Dear Sir/Mam,

Can anyone tell me select statement for ignoring the case.

For example I have entry Amarjit in table Names and i want to use select statement which takes name from a text box and shows the name even if the textbox contains all letters in small case or mixed case(e.g. amarjit, amaRJIt).

I want that the select statement should ignore the case while looking from the database table.
Also Please tell me How can i search whole table for some particular keyword.
Means if I have keyword "abc" how can i search if it exists in any row in any column in table and then how to select that column.

Thanks in advance for your kind help.