SQL statement?

Question

I need a select statement that will allow a user to search a database table by a name filed. But I need it to work only if they enter say the first 3 letters and not all of them so for example if someone typed in Jo it would return Joe, Jones, John ... is there any select statement with a where clause that would allow me to do this?

Answer 1

SELECT * FROM [tableName] WHERE [name] LIKE '%' + @name + '%'

Answer 2

I am taking the field name as name.

1) for names which start with Jo

select name from table name where name like 'Jo%' or name like 'jo%'

2) for names which have jo somewhere in between

select name from table_name where name like '%jo%'

3) for names which have jo in the end

select name from table_name where name like '%jo'

I guess the first statement would serve your purpose i included the other ones to explain how % works..what it actually does it acts as a wild card character matching any numbers of characters where it is placed..so for example in the first case it will match joe,jones,joul...and so on..

Answer 3

as an addition to Instinc and Mervinc :

To do case insensitive search, you could use 'Upper()' to make your searching field records to uppercase and compare it with your searching parameter, remember to set your search parameter to uppercase :
Select * from myTable where Upper(Firstname) like '%JOE%'

notes : check your database help to find the similar command with 'Upper' if your database didn't support it.

Answer 4

if working in access, try doing this querry using querry wizard, then after it works properly, go back and look at the sql generated by wizzard, not in access?....I don't know.

Answer 5

You will need to use like as the operator and * as the wildcard.