Get records beginning with certain letter

am 05.07.2006 15:11:45 von James Jones

Want to get recrods from an access database the begin with a certain letter.
Any way possible?

not exactly sure how to setup the SQL code for it.

thanks,
jay

Re: Get records beginning with certain letter

am 05.07.2006 15:49:14 von reb01501

James Jones wrote:
> Want to get recrods from an access database the begin with a certain
> letter. Any way possible?
>
> not exactly sure how to setup the SQL code for it.
>
select from tablename
where columnname like '%'

If testing this query in Access, change the % to *

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Get records beginning with certain letter

am 05.07.2006 15:57:08 von James Jones

o yea, lol duh! I forgot all about the LIKE function
thanks a lot!

jay

"Bob Barrows [MVP]" wrote in message
news:%23qanOnDoGHA.4736@TK2MSFTNGP02.phx.gbl...
> James Jones wrote:
>> Want to get recrods from an access database the begin with a certain
>> letter. Any way possible?
>>
>> not exactly sure how to setup the SQL code for it.
>>
> select from tablename
> where columnname like '%'
>
> If testing this query in Access, change the % to *
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Get records beginning with certain letter

am 05.07.2006 17:34:01 von James Jones

well how about for numbers? i have the option set for users to select "0-9"
how would i make that search for any number?

0-9% will not show what i want.........any idea

"James Jones" wrote in message
news:e634srDoGHA.1248@TK2MSFTNGP05.phx.gbl...
>o yea, lol duh! I forgot all about the LIKE function
> thanks a lot!
>
>
> jay
>
>
>
>
> "Bob Barrows [MVP]" wrote in message
> news:%23qanOnDoGHA.4736@TK2MSFTNGP02.phx.gbl...
>> James Jones wrote:
>>> Want to get recrods from an access database the begin with a certain
>>> letter. Any way possible?
>>>
>>> not exactly sure how to setup the SQL code for it.
>>>
>> select from tablename
>> where columnname like '%'
>>
>> If testing this query in Access, change the % to *
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get a
>> quicker response by posting to the newsgroup.
>>
>>
>
>

Re: Get records beginning with certain letter

am 05.07.2006 17:51:10 von reb01501

.... where
columnname>=0 and columnname <=9
James Jones wrote:
> well how about for numbers? i have the option set for users to select
> "0-9" how would i make that search for any number?
>
> 0-9% will not show what i want.........any idea
>
>
>
>
>
>
>
> "James Jones" wrote in message
> news:e634srDoGHA.1248@TK2MSFTNGP05.phx.gbl...
>> o yea, lol duh! I forgot all about the LIKE function
>> thanks a lot!
>>
>>
>> jay
>>
>>
>>
>>
>> "Bob Barrows [MVP]" wrote in message
>> news:%23qanOnDoGHA.4736@TK2MSFTNGP02.phx.gbl...
>>> James Jones wrote:
>>>> Want to get recrods from an access database the begin with a
>>>> certain letter. Any way possible?
>>>>
>>>> not exactly sure how to setup the SQL code for it.
>>>>
>>> select from tablename
>>> where columnname like '%'
>>>
>>> If testing this query in Access, change the % to *
>>>
>>> --
>>> Microsoft MVP -- ASP/ASP.NET
>>> Please reply to the newsgroup. The email account listed in my From
>>> header is my spam trap, so I don't check it very often. You will
>>> get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Get records beginning with certain letter

am 05.07.2006 20:09:29 von ten.xoc

WHAT DATABASE?

For SQL Server, you can say

WHERE columnname LIKE '[0-9]%'

"James Jones" wrote in message
news:O%23n9xhEoGHA.4688@TK2MSFTNGP03.phx.gbl...
> well how about for numbers? i have the option set for users to select
> "0-9" how would i make that search for any number?
>
> 0-9% will not show what i want.........any idea

Re: Get records beginning with certain letter

am 05.07.2006 21:14:56 von Mike Brind

Aaron Bertrand [SQL Server MVP] wrote:
> WHAT DATABASE?
>

Ermmmm... probably the Access one he mentioned in his OP...

;-)

Re: Get records beginning with certain letter

am 06.07.2006 02:26:32 von ten.xoc

> Ermmmm... probably the Access one he mentioned in his OP...

Sorry, scanned for proper nouns.

Re: Get records beginning with certain letter

am 06.07.2006 05:41:36 von James Jones

<%
SQL_Get_Videos = "Select * FROM videos WHERE Artist LIKE '" & ltr & "%'"
rs.Open SQL_Get_Videos , conn
IF rs.EOF Then
%>No videos by artist beginning with the letter <%=ltr%>
<%
Else
%>

how would i get it to search for it beginning with numbers?

ltr is defined by a querystring

if the user selects "0-9" then it should show any video beginning with the
numbers 0-9. But since ltr is defined by the querystring, i cant get it to
search for the string that i was given in previous post.

PLEASE HELP ME!!! lol

thanks a lot those who helped so far!!!

jay

"Bob Barrows [MVP]" wrote in message
news:e%23MTXrEoGHA.4688@TK2MSFTNGP03.phx.gbl...
> ... where
> columnname>=0 and columnname <=9
> James Jones wrote:
>> well how about for numbers? i have the option set for users to select
>> "0-9" how would i make that search for any number?
>>
>> 0-9% will not show what i want.........any idea
>>
>>
>>
>>
>>
>>
>>
>> "James Jones" wrote in message
>> news:e634srDoGHA.1248@TK2MSFTNGP05.phx.gbl...
>>> o yea, lol duh! I forgot all about the LIKE function
>>> thanks a lot!
>>>
>>>
>>> jay
>>>
>>>
>>>
>>>
>>> "Bob Barrows [MVP]" wrote in message
>>> news:%23qanOnDoGHA.4736@TK2MSFTNGP02.phx.gbl...
>>>> James Jones wrote:
>>>>> Want to get recrods from an access database the begin with a
>>>>> certain letter. Any way possible?
>>>>>
>>>>> not exactly sure how to setup the SQL code for it.
>>>>>
>>>> select from tablename
>>>> where columnname like '%'
>>>>
>>>> If testing this query in Access, change the % to *
>>>>
>>>> --
>>>> Microsoft MVP -- ASP/ASP.NET
>>>> Please reply to the newsgroup. The email account listed in my From
>>>> header is my spam trap, so I don't check it very often. You will
>>>> get a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Get records beginning with certain letter

am 06.07.2006 13:27:45 von reb01501

James Jones wrote:
> <%
> SQL_Get_Videos = "Select * FROM videos WHERE Artist LIKE '" & ltr &
> "%'" rs.Open SQL_Get_Videos , conn

Oh! Don't do this! You are leaving your database and website vulnerable to
hackers using SQL Injection
(http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23)

Use parameters, instead; preferably via a saved parameter query :
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd

or, use a Command object like this:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

If you use this technique, your sql string should be:
SQL_Get_Videos = "Select * FROM videos " & _
"WHERE Artist LIKE ? & '%'"

> IF rs.EOF Then
> %>No videos by artist beginning with the letter <%=ltr%>
> <%
> Else
> %>
>
>
>
> how would i get it to search for it beginning with numbers?
>
> ltr is defined by a querystring
>
> if the user selects "0-9" then it should show any video beginning
> with the numbers 0-9. But since ltr is defined by the querystring, i
> cant get it to search for the string that i was given in previous
> post.
>
It always helps to tell us the details. When you said "search for numbers" I
assumed you were talking about a numeric field. It never hurts to show us a
few rows of sample data, along with a description of the names and datatypes
of the fields involved.

So it sounds as if you have a Text field, containing strings some of which
begin with numbers: 0Abc, 3edf, etc. Correct?

Well, with Jet, you can do this:

" ... WHERE Left(fieldname,1) IN ('0','1', ..., '9')"
(you need to fill in the ... with the rest of the numbers)

This will not perform very well because it will not be able to use an index
if you have created one on the field. But then again, no other search method
will anyways. You would be better off creating a separate numeric field to
hold just the first character of the text you wish to search, creating an
index on that field. You could use the ascii code for the character, or
simply use -1 for alpha characters, and 0-9 for numeric characters.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"