locked
Exclude certain data RRS feed

  • Question

  • SELECT     *
    FROM         VW_MPSReport

    I want to exclude some of the data given in this query, I want all selected but in PROPERTY_Path I want to exclude items that begin with \SiteElements and \IMAGELIBRARY

    So basically I want it to show everything that doesn't begin with \SiteElements and \IMAGELIBRARY

    Can somebody help?


    • Edited by CaseyAnne Tuesday, November 20, 2012 2:17 PM
    Tuesday, November 20, 2012 11:49 AM

Answers

  • SELECT     *
    FROM         VW_MPSReport

    WHERE PROPERTY_PATH NOT LIKE '[\]SiteElements%' AND Property_Path NOT LIKE '[\]IMAGELIBRARY%'

    Are you using SQL Server?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, November 21, 2012 3:19 AM

All replies

  • SELECT     *
    FROM         VW_MPSReport

    WHERE  PROPERTY_Path  NOT LIKE '\SiteElements and \IMAGELIBRARY%'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Proposed as answer by Kalman Toth Tuesday, November 20, 2012 12:42 PM
    • Unproposed as answer by Naomi N Wednesday, November 21, 2012 3:16 AM
    Tuesday, November 20, 2012 11:52 AM
    Answerer
  • Try the below one

    SELECT     *
    FROM   VW_MPSReport Where PROPERTY_Path NOT LIKE '\SiteElements and \IMAGELIBRARY%'
    -------------------------------
    ---------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Kalman Toth Tuesday, November 20, 2012 12:42 PM
    Tuesday, November 20, 2012 11:56 AM
  • if \SiteElements and \IMAGELIBRARY are different checking criteria's then use
    SELECT * FROM VW_MPSReport 
    WHERE  PROPERTY_Path NOT LIKE '\SiteElements%' and PROPERTY_Path NOT LIKE '\IMAGELIBRARY%'
    else use the previous posts
    • Proposed as answer by Kalman Toth Tuesday, November 20, 2012 12:43 PM
    Tuesday, November 20, 2012 12:09 PM
  • use like in WHERE clause. LIKE Determines whether a specific character string matches a specified pattern.

    for eg.

    1) if you want to find a word exists the use % at both the end like

    where column like '%\SiteElements%'

    2) if you want to find records that ends with particular word then use % only at the begining

    where column like '%\SiteElements'

    3) if you want to find records that begins with partucular word then use % only at the begining

    where column like '\SiteElements%'

    For more help please refer the below URL
    http://msdn.microsoft.com/en-us/library/ms179859.aspx

    Try the below query..

    SELECT     *
    FROM VW_MPSReport
    WHERE  PROPERTY_Path  NOT LIKE '\SiteElements and \IMAGELIBRARY%'

    Let me know it works for you.

    Thanks.

    Best Regards, Venkat

    Tuesday, November 20, 2012 12:31 PM
  • Thanks, I've tried all these but they didn't work.
    • Edited by CaseyAnne Tuesday, November 20, 2012 2:57 PM
    Tuesday, November 20, 2012 2:15 PM
  • SELECT     *
    FROM         VW_MPSReport

    WHERE PROPERTY_PATH NOT LIKE '[\]SiteElements%' AND Property_Path NOT LIKE '[\]IMAGELIBRARY%'

    Are you using SQL Server?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, November 21, 2012 3:19 AM
  • yes, I am using SQL Server 2005
    Wednesday, November 21, 2012 10:04 AM
  • Thanks, that worked. The slashes seemed to be the wrong way round.
    Wednesday, November 21, 2012 10:06 AM