Solved

Include bracket inside sql text string

Posted on 2004-04-29
8
664 Views
Last Modified: 2008-03-03
To screen out mobile phone numbers,
In a WHERE clause I want say :
AND phone_number not like "(04)*"
How should this be coded so the brackets are not confused with other brackets in the statement?
0
Comment
Question by:andrewcurtis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10946738
Hmm, this LIKE will work the same as :
WHERE LEFT([phone_number],4) <> "(04)"

And will require that the (04) is in the first four positions, is that what you need ?

Nic;o)
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10946745
Hmm, into an SQL string all needed would be to use single quotes like:
strSQL = "select * from tblX where phone_number not like '(04)*';"

Nic;o)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10946749
Hi andrewcurtis,

I think you have done what you asked in your example.

Presumably your phone_number field has a data type of Text or else the entries would not be able to include brackets.


The extraneous brackets inserted by the MS-Access Query By Design builder are not needed:
SELECT * FROM Table1
WHERE (((Table1.Field1)="something") AND ((Table1.phone_number) Not Like "(04)*"));


Your clearer SELECT statement would be along the lines of:
SELECT * FROM Table WHERE Field1 = "something" AND phone_number Not Like "(04)*"


PS. Are you in the UK [I'm guessing so as you didn't say 'Parenthesis' :)]?

Mobile numbers begin with 07...



BFN,

fp.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:jjafferr
ID: 10946788
Hi andrewcurtis,

I am not good in SQL, but worth giving it a try,
why not define it with a variable, then put this variable in the SQL.
iNumber="(04)*"
strSQL = "select * from tblX where phone_number not like iNumber";

I hope this helps,

jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10947009
phone_number not like "(04)*"

this will records where the phone number does not start with (04), is that what you are after?

perhaps, because you got brackets, its a string field, you put single quotes in (like Nico has done)
for string comparisons, you got to surround it with quotes

 phone_number not like '(04)*'

to do a match on anything with (04) in, put an asterix in front as well ie  *(04)*





0
 
LVL 4

Expert Comment

by:AndyAmess
ID: 10947338
Andrew Curtis,

This is a common problem.

 "......AND phone_number not like " &   Chr$(34) & "(04)*" &  Chr$(34)

Because the brackets will now be in quotes they will not be confused with non-text brackets.

I usually have the procedure below and call it like

"......AND phone_number not like " &  Quotes("(04)*")

=====================================
Public Function Quotes(aText As String)
    Quotes = Chr$(34) & aText & Chr$(34)
End Function


A. ;-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question