SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    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'/&'"

  2. #2
    SitePoint Wizard siteguru's Avatar
    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?
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)&"_%' "

  4. #4
    SitePoint Member
    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

  5. #5
    SitePoint Wizard siteguru's Avatar
    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) & "%'"
    ???
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    SitePoint Member
    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_%'

  7. #7
    SitePoint Wizard siteguru's Avatar
    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.
    Ian Anderson
    www.siteguru.co.uk

  8. #8
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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) & "%' "

  9. #9
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,255
    Mentioned
    196 Post(s)
    Tagged
    2 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 ??

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    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
    */
    as expected
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    358
    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

  13. #13
    SitePoint Wizard siteguru's Avatar
    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?)
    Ian Anderson
    www.siteguru.co.uk

  14. #14
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,255
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by siteguru View Post
    .....
    @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.

  15. #15
    SitePoint Member
    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

Posting Permissions

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