LIVE NOW: GET-IT: Endpoint Management 1-Day Conference LIVE NOW: GET-IT: Endpoint Management 1-Day Conference

Query to return all text up until a perticular characher

Home Forums Other Microsoft Servers and SaaS SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2016 Query to return all text up until a perticular characher

Viewing 1 post (of 1 total)
  • Author
    Posts

  • 5habbaranks
    Member
    #166988

    Hi guys,

    Im trying to create a query which returns all text up until the first ” so for example here is my current query

    [FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]SELECT[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF]SUBSTRING[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]Path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][FONT=Consolas][SIZE=10px]17[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][FONT=Consolas][SIZE=10px]45[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080])[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]as[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][FONT=Consolas][SIZE=10px] Data[/SIZE][/FONT][/SIZE][/FONT]
    [FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]FROM[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][FONT=Consolas][SIZE=10px] Table[/SIZE][/FONT][/SIZE][/FONT]
    [FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]WHERE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF][FONT=Consolas][SIZE=10px][COLOR=#0000FF]Path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080]like[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000]’Enq%'[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080]AND[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
    [FONT=Consolas][SIZE=10px][FONT=Consolas][SIZE=10px]Deleted [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080][FONT=Consolas][SIZE=10px][COLOR=#808080]=[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=10px][COLOR=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000]’0′[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/CODE]

    Current output is

    ENQ-000017-Testing1. Test FolderData 1Data 2
    ENQ-000018-Testing1. Test FolderData 1Test
    ENQ-000018-Testing1. Test FolderData 1TestData 3
    ENQ-000019-Testing1. Test Folder
    ENQ-000019-Testing1. Test Folder
    ENQ-000019-Testing1. Test FolderTest Folder 2
    ENQ-000020-Testing1. Test FolderTest Folder 3

    But I only want the text up until the first backslash so results should be

    ENQ-000017-Testing
    ENQ-000018-Testing
    ENQ-000019-Testing
    ENQ-000020-Testing

    Any thoughts how to achieve this?

    Thanks as always :)[CODE][SIZE=10px][SIZE=10px][SIZE=10px]SELECT[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]path[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px],[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]SUBSTRING[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]([/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]Path[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px],[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px]17[/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px],[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px]45[/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px])[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]as[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px] Data[/SIZE][/SIZE]
    [SIZE=10px][SIZE=10px][SIZE=10px]FROM[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px] Table[/SIZE][/SIZE]
    [SIZE=10px][SIZE=10px][SIZE=10px]WHERE[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]Path[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]like[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]‘Enq%’[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]AND[/SIZE][/SIZE][/SIZE]
    [SIZE=10px][SIZE=10px]Deleted [/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]=[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]‘0’[/SIZE][/SIZE][/SIZE][/CODE]

    Current output is

    ENQ-000017-Testing1. Test FolderData 1Data 2
    ENQ-000018-Testing1. Test FolderData 1Test
    ENQ-000018-Testing1. Test FolderData 1TestData 3
    ENQ-000019-Testing1. Test Folder
    ENQ-000019-Testing1. Test Folder
    ENQ-000019-Testing1. Test FolderTest Folder 2
    ENQ-000020-Testing1. Test FolderTest Folder 3

    But I only want the text up until the first backslash so results should be

    ENQ-000017-Testing
    ENQ-000018-Testing
    ENQ-000019-Testing
    ENQ-000020-Testing

    Any thoughts how to achieve this?

    Thanks as always :)

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: