SQL Wildcard Question

Sorry I am new to working with databases - I am trying to perform a query that will get all of the characters that are similar to a string in SQL.

For example,

If I am looking for all users that begin with a ce开发者_开发百科rtain string, something like S* or Sm* that would return "Smith, Smelly, Smiles, etc..."

Am I on the right track with this?

Any help would be appreciated, and Thanks in advance!

Sql can't do this ... placing the percentage symbol in the middle.

SELECT * FROM users WHERE last_name LIKE 'S%th' 

you would need to write a where clause and an and clause.

 SELECT * FROM users WHERE last_name LIKE 'S%' and last_name LIKE '%th' 

The LIKE operator is what you are searching for, so for your example you would need something like:

SELECT * 
  FROM [Users]
 WHERE LastName LIKE 'S%'

The % character is the wild-card in SQL.

to get all the users with a lastname of smith

SELECT * 
  FROM [Users]
 WHERE LastName ='Smith'

to get all users where the lastname contains smith do this, that will also return blasmith, smith2 etc etc

SELECT * 
  FROM [Users]
 WHERE LastName LIKE '%Smith%'

If you want everything that starts with smith do this

SELECT * 
  FROM [Users]
 WHERE LastName LIKE 'Smith%'

Standard (ANSI) SQL has two wildcard characters for use with the LIKE keyword:

• _ (underscore). Matches a single occurrence of any single character.
• % (percent sign). Matches zero or more occurrences of any single character.

In addition, SQL Server extends the LIKE wildcard matching to include character set specification, rather like a normal regular expresion character set specifier:

• [character-set] Matches a single character from the specified set
• [^character-set] Matches a single character not in the specified set.

Character sets may be specified in the normal way as a range as well:

• [0-9] matches any decimal digit.
• [A-Z] matches any upper-case letter
• [^A-Z0-9-] matches any character that isn't a letter, digit or hyphen.

The semantics of letter matching of course, a dependent on the collation sequence in use. It may or may not be case-sensitive.

Further, to match a literal left square bracket ('[]'), you must use the character range specifier. You won't get a syntax error, but you won't get a match, either.

where x.field like 'x[[][0-9]]'

will match text that looks like 'x[0]' , 'x[8]', etc. But

where 'abc[x' like 'abc[x'

will always be false.

you might also like the results of SOUNDEX, depending on your preference for last name similarity.

select * 
from [users]
where soundex('lastname') = soundex( 'Smith' )
or upper(lastname) like 'SM%'

Your question isn't entirely clear.

If you want all the users with last name Smith, a regular search will work:

 SELECT * FROM users WHERE last_name = 'Smith'

If you want all the users beginning with 'S' and ending in 'th', you can use LIKE and a wildcard:

 SELECT * FROM users WHERE last_name LIKE 'S%th'

(Note the standard SQL many wildcard of '%' rather than '*').

If you really want a "sounds like" match, many databases support one or more SOUNDEX algorithm searches. Check the documentation for your specific product (which you don't mention in the question).