SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
Thread: escaping underscore _
-
Apr 15, 2011, 08:21 #1
- Join Date
- Apr 2011
- Posts
- 14
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
escaping underscore _
How does one escape an _ when selecting data from sql database using a variable. All data with _ is not showing. I tried this but it is not working. Is there another way? words before and after the underscore.
WHERE acct LIke "'&/_%'ESCAPE'/&myArray(i)/_%'ESCAPE'/&'"
-
Apr 15, 2011, 08:55 #2
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Underscore is just a regular character - it does not need any special handling. So I'm confused as to your issue. How about showing us the code you're trying to use, plus some example database data?
-
Apr 15, 2011, 10:24 #3
Your question is not clear. But what I understand is that "ACCOUNT" column in database contains data something like "_USER_ADMIN_SUPERVISOR_". If this is the case the following query may help:
Where_Clause = " ACCOUNT = '%_"&array(i)&"_%' "
-
Apr 15, 2011, 12:32 #4
- Join Date
- Apr 2011
- Posts
- 14
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks, the data does includes eg book_work or b_shop or big_school_chair or 4_computer or just table
I tried
where_clause = "Acc_code LIKE " &"'%_"&myArray(i)&"_%'"
but this returned when printed out
WHERE acc_code LIKE '%_b_shop_%' and did not give me the data.
All data without the acc_code including _ is returned when selected, but when data with acc_code including underscore is selected, that data is not returned
-
Apr 15, 2011, 12:42 #5
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Code:where_clause = "Acc_code LIKE '%" & myArray(i) & "%'"
-
Apr 15, 2011, 12:42 #6
- Join Date
- Apr 2011
- Posts
- 14
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I tried it also with the equals sign as well
where_clause = "acc_code = " &"'%_"&myArray(i)&"_%'"
and got the same result when printed out
acct_code = '%_b_shop_%'
-
Apr 15, 2011, 12:54 #7
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
#1 the % delimiter is only relevant when LIKE is used.
#2 you haven't tried my example.
-
Apr 16, 2011, 09:52 #8
As you mentioned that the data can be "book_work or b_shop or big_school_chair or 4_computer or just table" so in your case the following query should work:
where_clause = "ACCOUNT LIKE '%" & myArray(i) & "%' "
-
Apr 16, 2011, 16:10 #9
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 Thread(s)
Not sure, but AFAIK for MySQL "%" is a wiildcard for "0 or more any characters" and "_" is a wildcard for "any one character".
Seems "_" would be considered a single character, but maybe not?
Maybe try backslash escapes like
word\_word\_word ??Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Apr 16, 2011, 16:25 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Apr 16, 2011, 16:32 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:CREATE TABLE test_underscore ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , foo VARCHAR(99) ); INSERT INTO test_underscore (foo) VALUES ( 'b-shop-dash' ) ,( 'b shop space' ) ,( 'b_shop_underscore' ) ,( 'b__shop__2underscores' ) ; SELECT * FROM test_underscore WHERE foo LIKE 'b_shop%'; /* id foo 1 b-shop-dash 2 b shop space 3 b_shop_underscore */ SELECT * FROM test_underscore WHERE foo LIKE 'b\_shop%'; /* id foo 3 b_shop_underscore */
-
Apr 16, 2011, 20:07 #12
- Join Date
- Apr 2009
- Posts
- 359
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Perhaps the underscore doesn't ever make it into the db? Some applications do some 'validation' on sql strings by removing any possibly offending characters. You might check with your database query tool and verify the data string does in fact contain underscore(s).
Doug G
=====
"If you ain't the lead dog, the view is always the same - Anon
-
Apr 17, 2011, 09:13 #13
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
@itHighway - your suggestion essentially repeats what I posted 21 hours prior to your post.
@Mittineague - I can't see any mention that elizann10 is using Mysql?
@Doug G - possibly.
@elizann10 - like Doug suggests, what is the data that is actually stored in the database table? (Not what you think is stored, what is actually stored?)
-
Apr 17, 2011, 11:58 #14
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 Thread(s)
But as Rudy stated ".... when used in LIKE strings and it's not just mysql, it's standard sql"
The reason I mentioned MySQL was not because I thought the OP was using MySQL but because I thought there might be a chance that whatever db is being used it might also use the same as a wildcard.
Not that this is the problem, more just wondering aloud if it might have something to do with it.Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Apr 18, 2011, 08:47 #15
- Join Date
- Apr 2011
- Posts
- 14
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks everybody for your suggestions
I am using Microsoft sql server 2005, The data is in fact in the database with the underscores.
I have tried your suggestions but still not able to select the data, not sure what is happening.
Bookmarks