escaping underscore _

[elizann10]

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'/&'"

[siteguru]

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?

[itHighway]

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)&"_%' "

[elizann10]

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

[siteguru]

where_clause = "Acc_code LIKE '%" & myArray(i) & "%'"

???

[elizann10]

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_%'

[siteguru]

#1 the % delimiter is only relevant when LIKE is used.

#2 you haven't tried my example.

[itHighway]

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) & "%' "

[Mittineague]

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 ??

[r937]

Seems "_" would be considered a single character

it is, yes, a single character when used in LIKE strings

and it's not just mysql, it's standard sql

mind you, the underscore is a single character, so i'm not sure why the data isn't being returned

[r937]

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
*/

as expected

[Doug G]

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).

[siteguru]

@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?)

[Mittineague]

.....
@Mittineague - I can't see any mention that elizann10 is using Mysql?
.....

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.

[elizann10]

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.