?
Solved

Include bracket inside sql text string

Posted on 2004-04-29
8
Medium Priority
?
673 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

770 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