Open Forum

Expand all | Collapse all

SQL People, this question is for you!

  • 1.  SQL People, this question is for you!

    GOLD CONTRIBUTOR
    Posted 11 days ago
    We have item numbers in our database, that start with an underscore. They were setup years ago, not by me. So when I filter by item numbers, I want to exclude those when using a wildcard like statement.

    So I know that I can't write, WHERE ITEMNMBR NOT LIKE '_ITEM1234%'

    How can I write it?

    ------------------------------
    Chase Landorf
    Director of I.T.
    Ideal Deals, LLC dba Ideal Aluminum Products
    Saint Augustine FL
    ------------------------------


  • 2.  RE: SQL People, this question is for you!

    GOLD CONTRIBUTOR
    Posted 11 days ago

    I answered my own question. If my item number is _ITEM123, and my item list has a bunch that have the underscore in it, then you need to use [ ] around the character, to let sql know it is actually a character.

    HEADER

    ITEM1
    ITEM13
    _ITEM1234
    _ITEM154

    WHERE ITEMNMBR NOT LIKE '[_]%'

    It will return ITEM1 and ITEM13

    ------------------------------
    Chase Landorf
    Director of I.T.
    Ideal Deals, LLC dba Ideal Aluminum Products
    Saint Augustine FL
    ------------------------------



  • 3.  RE: SQL People, this question is for you!

    Posted 11 days ago
    You can also use the escape keyword and specify an escape character.  For example:

    WHERE ITEMNMBR NOT LIKE '!_%' ESCAPE '!'

    ------------------------------
    Sarah Brewster
    Implementation and Support
    nQativ
    Lubbock TX
    ------------------------------



  • 4.  RE: SQL People, this question is for you!

    Posted 6 days ago
    Edited by Sandeep C 6 days ago
    You're absolutely right @Chase Landorf

    WHERE ITEMNMBR NOT LIKE '[_]%'
    ​​

    I would recommend @Sarah Brewster Escape Keyword which is used to escape pattern matching characters such as the (%)  and (_). 


    ------------------------------
    Sandeep
    SQL DBA
    Chase Petroleum Services
    Artesia NM
    ------------------------------