Use of [ and ] in SQL - can't stop the wildcard usage


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Use of [ and ] in SQL - can't stop the wildcard usage

  1. #1
    Join Date
    May 2006
    Posts
    3

    Use of [ and ] in SQL - can't stop the wildcard usage

    I have a select query that is attempting to retrieve a field:

    Code:
    SQL = "select * from tagtable where Fname like '*" & FieldString & "*'"
    If FieldString = "A N Other [21]" then

    Code:
    SQL = "select * from tagtable where Fname like '*A N Other [21]*'"
    which includes square brackets that are real characters wthin the field but are interpreted as wildcards. I've tried using various combinations of quotes and other symbols but none work.

    How can I interpret the brackets as part of the string and not wildcards?

  2. #2
    Join Date
    Aug 2005
    Posts
    51
    Perhaps you could use the ascii values for those characters:

    [ ascii value is 91
    ] ascii value is 93

  3. #3
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    If you are using Access, then look up help on 'Like'. If you do not have it, then the following may help ...

    'The following example returns data that begins with the letter P followed by any letter between A and F and three digits:

    Like "P[A-F]###"

    Thus, '[' is a special character ...

    'Brackets ([ ]) around a field, control, or property in an identifier indicate that the element is the name of a table, query, form, report, field, or control.'

    Have no solution at hand as to how you search if '[' is a char within a field, but, unless someone else can provide the answer beforehand, will try and find the answer in the office in the morning ....

  4. #4
    Join Date
    May 2006
    Posts
    3
    Substituting the Ascii codes didn't work because the query took the ascii codes to be the characters, ie: ascii 91 is [, so same problem.

    I tried putting square brakets around the square brackets, ie: [ becomes [[] and ] becomes []]. But this has its pitfalls:

    I tried this with:
    Code:
    SQL = Replace(SQL, "[", "[[]")
    SQL = Replace(SQL, "]", "[]]")
    which didn't work, but I just realised that the first replace adds a second ] which really is a wildcard, which also got turned into a []], ie:
    Code:
    SQL = "select * from tagtable where Fname like '*A N Other [21]*'"
    SQL = Replace(SQL, "[", "[[]")
    SQL = Replace(SQL, "]", "[]]")
    so SQL = "select * from tagtable where Fname like '*A N Other [[[]]Rem []]*'" 
    ' too many brackets
    To stop the extra replace I have to do this:
    Code:
    SQL = Replace(SQL, "[", "tempstr to be replaced in a second")
    SQL = Replace(SQL, "]", "[]]")
    SQL = Replace(SQL, "tempstr to be replaced in a second", "[[]")
    which gives me:
    Code:
    SQL = "select * from tagtable where Fname like '*A N Other [[]Rem []]*'"
    Which works.

    Thanks for your time. Though you didn't answer my question directly, it pushed me in the right direction.

Similar Threads

  1. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 11:04 AM
  2. Blocking problem with DSN-less connection
    By Adam Dawes in forum VB Classic
    Replies: 3
    Last Post: 12-21-2000, 12:50 PM
  3. Replies: 0
    Last Post: 10-06-2000, 04:48 AM
  4. Replies: 0
    Last Post: 06-22-2000, 08:30 AM
  5. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 01:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center