Results 1 to 11 of 11

Thread: searching for special character doesnt return anythins

  1. #1

    Thread Starter
    Fanatic Member coolcurrent4u's Avatar
    Join Date
    Apr 2008
    Location
    *****
    Posts
    993

    searching for special character doesnt return anythins

    i saved and am searching for all string " ! @ # $ % ^ & * ( ) '" symbols in within a field content a database, using the following sql string

    Code:
    Select ID From Contacts Where '--' || note || '--'  like '%! @ # $ % ^ & * ( ) ""%'
    i need a way to either escape all the characters, or encode them so search is easier
    Programming is all about good logic. Spend more time here


    (Generate pronounceable password) (Generate random number c#) (Filter array with another array)

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: searching for special character doesnt return anythins

    1-Does that SQL statement work (I assume it is in MS ACCESS)?
    2-If Not, then this thread belongs in the Office (for MS ACESS), or the Database, fora, for a better query.
    3-If Yes, what field(s) are you searching within (note?)?

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: searching for special character doesnt return anythins

    This query finds any record with a $ in my 'myField' field. (Chr$(36) is the ASCII equivalent of a dollar sign).
    You might be able to loop several queries using each ASCII numeric for each of your special characters, or put in 'or' in the query:

    "select ID from contacts where myField like '%' & chr$(36) & '%'"

  4. #4

    Thread Starter
    Fanatic Member coolcurrent4u's Avatar
    Join Date
    Apr 2008
    Location
    *****
    Posts
    993

    Re: searching for special character doesnt return anythins

    thanks for responding, but am earching not for a single characer, but the whole set as seen in my question. this was saved ealier in the database
    Programming is all about good logic. Spend more time here


    (Generate pronounceable password) (Generate random number c#) (Filter array with another array)

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: searching for special character doesnt return anythins

    Does that whole set exist together as typed in the query?

    What is this supposed to be '--' || note || '--' ? It would be expecting a field name there. I would expect that to through an error

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: searching for special character doesnt return anythins

    If a 'whole set', as DM asked, then that is simply a string. But I imagine, you want to search for EACH of the spec chars in that set.
    You can do that by using < > (less than, greater than) in the chr$(ASCII-NUMBER) for those that fall within a certain range. If any others outside that range, simply and an 'or' in your statement like this:
    "select ID from contacts where myField like '%' & chr$(36) & '%' or myField like '%' & chr$(41) & '%'"
    Add more 'ors' for each character.

    Most of those characters fall between 33 and 47, so you can get most by using the < 47 and >33 symbols in your query.

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: searching for special character doesnt return anythins

    The % and/or the * may be an issue depending on the DB engine in use and if you are using DAO or the preferred ADO.

    Also as I pointed out before

    '--' || note || '--'

    Is the strangest looking field name I have ever saw. I can't imagine anyway that could work without giving an error. If that is your actual field name then you should seriously consider changing it. As a general rule you should not use spaces in any field or table names. You should also not use special characters nor reserved words in either type of name. If a name does have a space or is a reserved word then you need to bracket it with [ ] in your sql statement

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: searching for special character doesnt return anythins

    @DataMiser....aye, those two chars DO provide a challenge when searching for them in ACCESS with ADO. However, am sure there is an easy way to fool access and get the return OP needs. Personally never attempted looking for spec chars, but, hey, I guess situations could come up easily. Waiting on OP's response to get a better picture of what he/she needs----loops will work, as will ranges of chr()s.

  9. #9

    Thread Starter
    Fanatic Member coolcurrent4u's Avatar
    Join Date
    Apr 2008
    Location
    *****
    Posts
    993

    Re: searching for special character doesnt return anythins

    hello,
    Code:
    note
    is the field name, the
    Code:
    ||
    is a concatenation operator so if a note value is
    Code:
    some note
    , then in the select, it it concatenated as
    Code:
    --some note--
    . this technique is to help me search in all fields at once. i simple just concatenate them as one string and look in that string for what am looking for
    Programming is all about good logic. Spend more time here


    (Generate pronounceable password) (Generate random number c#) (Filter array with another array)

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: searching for special character doesnt return anythins

    Ok.....so, have you attempted anything we have suggested yet?

  11. #11

    Thread Starter
    Fanatic Member coolcurrent4u's Avatar
    Join Date
    Apr 2008
    Location
    *****
    Posts
    993

    Re: searching for special character doesnt return anythins

    please note that the database is sqlite
    Last edited by coolcurrent4u; Feb 13th, 2013 at 09:27 AM.
    Programming is all about good logic. Spend more time here


    (Generate pronounceable password) (Generate random number c#) (Filter array with another array)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width