Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Include bracket inside sql text string

Posted on 2004-04-29
8
Medium Priority
?
683 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
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 2000 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
Independent Software Vendors: 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

963 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