Include bracket inside sql text string

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?
andrewcurtisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nico5038Connect With a Mentor Commented:
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
 
nico5038Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jjafferrCommented:
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
 
rockiroadsCommented:
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
 
AndyAmessCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.