SQL Question

[Altecjjf]

I would like to use a wildcard on this search for a user's last name. I know i can use the LIKE command, but i am not sure where to add it. If there is another way to do wildcard search could you please let me know. Thanks

Altecjjf VB5

Select * from Customers where ContactLastName = '" & UserInputByLastName & "'"

Thanks

[JasonGS]

"%" is the SQL wildcard, say your looking for MacDonald but you dont know if it was entered as Mac or Mc, you could do %donald%

SELECT * FROM customers WHERE contactlastname LIKE '%" & Text1.Text & "%';"

[Chris]

I think in Access the wildcard is * but in SQL server is %. If I'm wrong to let me know about it.

Thanks

[Stevie]

The wildcard in Access is %.

[Chris]

Thanks Stevie, I just found this from the MSDN.

Like Operator
*
Matches any number of characters, and can be used anywhere in the character string.
Example:
wh* finds what, white, and why
*at finds cat, bat, and what

?
Matches any single character.
Example:
b?ll finds ball, bell, and bill

#
Matches any single digit.
Example:
1#3 finds 103, 113, 123

[ ]
Matches any single character within the brackets.
Example:
b[ae]ll finds ball and bell but not bill


!
Matches any character not in the list.
Example:
b[!ae]ll finds bill and bull but not bell or ball


-
Matches any one of a range of characters.
Example:
b[a-c]d finds bad, bbd, and bcd

but you also right, because the MSDN also mention about the % wilcard. So may I know what is the diff between the * and % wilcard? and when should use?


LIKE Operator (T-SQL)
%
Any string of zero or more characters.
Example:
WHERE title LIKE ¡¥%computer%¡¦ finds all book titles with the word ¡¥computer¡¦
anywhere in the book title.

_
(underscore) Any single character.
Example:
WHERE au_fname LIKE ¡¥_ean¡¦ finds all four-letter first names that end with
ean (Dean, Sean, and so on).

[ ]
Any single character within the specified range ([a-f]) or set ([abcdef]).
Example:
WHERE au_lname LIKE ¡¥[C-P]arsen¡¦ finds author last names ending with arsen
and beginning with any single character between C and P, for example Carsen,
Larsen, Karsen, and so on.

[^]
Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
Example:
WHERE au_lname LIKE ¡¥de[^l]%¡¦ all author last names beginning with de and where
the following letter is not l.


Thanks

[Altecjjf]

Thank for everybody's help