SQL "LIKE" clause help

[stevepuri]

Hey,

I've got this SQL code that works fine for searching a field called "Surname" in a table called "tblCustomer" for
the character "S".

SELECT * FROM tblCustomer
WHERE Surname LIKE 'S'
ORDER BY Surname;

The problem is, that I get all the surnames that contain the "S" character like "Jone(s)" and "A(s)tury" etc.
I just want the surnames that start with "S"
only like "(S)mith" and "(S)tevenson" etc.

Any ideas?

Thanks,
Steve.

 

[braindead2]

SELECT * FROM tblCustomer
WHERE Surname LIKE 'S*'
ORDER BY Surname;

 

[stevepuri]

Hey,

I tried adding on the * but it
now gives no results whatsoever!

Any other ideas?

Thanks,
Steve.

 

[stevepuri]

I'll give you the "real" problem code.
I am using this query in VBScript as
part of an ASP page:

Dim query
Dim query1

'query variable below
'contains "S" or another character
query = Request.Form("txtQuery")

'query1 below contains "S*"
'or another character and *
query1 = query & "*"

Dim sql
sql = ""
sql = sql & "SELECT * FROM tblCustomer "
sql = sql & "WHERE Surname LIKE '%" & query1 & "%'"
sql = sql & "ORDER BY Surname;"

I've also tried % instead of * and it dont work!

Thanks,
Steve.

 

[WingandaPrayer]

Steve,

Going back to the original post this works.

SELECT tblCustomer.Surname
FROM tblCustomer
WHERE (((tblCustomer.Surname) Like "s*&quot)
GROUP BY tblCustomer.Surname

I used these names

jones
smith
roberts
msith
tripp
dean
seabury

Only these names appear in the query

seabury
smith

Whether this helps.

David

 

[Tim1]

Hi Steve,

You just need to change your statement slightly to give:

Dim sql
sql = ""
sql = sql & "SELECT * FROM tblCustomer "
sql = sql & "WHERE Surname LIKE " query1 & "%'"
sql = sql & "ORDER BY Surname;"

The like statement that you had ( sql = sql & "WHERE Surname LIKE '%" & query1 &"%'" will give a LIKE clause of '%S%' which will look for any name with an S in it, where as you want LIKE 'S%' which will only look for names that begin S

HTH

Tim