Open Forum

Expand all | Collapse all

SQL People, this question is for you!

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

    GOLD CONTRIBUTOR
    Posted Mar 14, 2019 01:46 PM
    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 Mar 14, 2019 02:11 PM

    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 Mar 14, 2019 02:21 PM
    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 Mar 19, 2019 01:58 PM
    Edited by Sandeep C Mar 19, 2019 01:59 PM
    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
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role