-
Feb 12th, 2013, 07:04 AM
#1
Thread Starter
Fanatic Member
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
-
Feb 12th, 2013, 07:48 AM
#2
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?)?
-
Feb 12th, 2013, 07:59 AM
#3
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) & '%'"
-
Feb 12th, 2013, 08:22 AM
#4
Thread Starter
Fanatic Member
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
-
Feb 12th, 2013, 08:27 AM
#5
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
-
Feb 12th, 2013, 08:46 AM
#6
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.
-
Feb 12th, 2013, 03:36 PM
#7
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
-
Feb 12th, 2013, 04:17 PM
#8
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.
-
Feb 13th, 2013, 08:58 AM
#9
Thread Starter
Fanatic Member
Re: searching for special character doesnt return anythins
hello, is the field name, the is a concatenation operator so if a note value is , then in the select, it it concatenated as . 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
-
Feb 13th, 2013, 09:01 AM
#10
Re: searching for special character doesnt return anythins
Ok.....so, have you attempted anything we have suggested yet?
-
Feb 13th, 2013, 09:18 AM
#11
Thread Starter
Fanatic Member
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|