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

This topic contains 13 replies, has 3 voices, and was last updated by  Dext 1 year, 7 months ago.

Viewing 14 posts - 1 through 14 (of 14 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 :)


    Ossian
    Moderator
    #191632

    Use CHARINDEX (SQL 2008 or higher):
    https://docs.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql


    Dext
    Member
    #379112

    Thanks I looked at charindex but it only returns the first letternumber rather than everything up until the next backslash

    [FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]substring[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]17[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]CHARINDEX[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]”[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Data[/SIZE][/FONT][/SIZE][/FONT]
    [FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Table[/SIZE][/FONT][/SIZE][/FONT]
    [FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Path[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]like[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]’ENQ%'[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT]
    [FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]Deleted [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]’0′[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
    [/CODE]

    So results are
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]Path[/TD]
    [TD]Data[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014Test Data 123Testing ABC[/TD]
    [TD]T[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014New FolderAnother Folder[/TD]
    [TD]N[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014ABC123123 567Test[/TD]
    [TD]A[/TD]
    [/TR]
    [/TABLE]

    But I would like
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD] [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]Path[/TD]
    [TD]Data[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014Test Data 123Testing ABC[/TD]
    [TD]Test Data 123[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014New FolderAnother Folder[/TD]
    [TD]New Folder[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014ABC123123 567Test[/TD]
    [TD]ABC123[/TD]
    [/TR]
    [/TABLE]
    [/TD]
    [TD] [/TD]
    [/TR]
    [/TABLE]

    [CODE]SELECT path, substring(path,17,CHARINDEX(,path)) As Data
    FROM Table
    WHERE Path like ‘ENQ%’ AND
    Deleted = ‘0’
    [/CODE]

    So results are
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]Path[/TD]
    [TD]Data[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014Test Data 123Testing ABC[/TD]
    [TD]T[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014New FolderAnother Folder[/TD]
    [TD]N[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014ABC123123 567Test[/TD]
    [TD]A[/TD]
    [/TR]
    [/TABLE]

    But I would like
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD] [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]Path[/TD]
    [TD]Data[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014Test Data 123Testing ABC[/TD]
    [TD]Test Data 123[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014New FolderAnother Folder[/TD]
    [TD]New Folder[/TD]
    [/TR]
    [TR]
    [TD]Enquiries2014ABC123123 567Test[/TD]
    [TD]ABC123[/TD]
    [/TR]
    [/TABLE]
    [/TD]
    [TD] [/TD]
    [/TR]
    [/TABLE]


    Ossian
    Moderator
    #191634

    I managed it with SELECT LEFT(path, CHARINDEX(”, path)) AS LeftPath – adapt as appropriate


    Dext
    Member
    #379113

    Thanks will give it a bash


    Dext
    Member
    #379114

    Hi,
    I tried it but no worky – if I use the SELECT LEFT without the substring it gives me the initial which the substring filters out. Is there anyway to select left after a substring?


    Ossian
    Moderator
    #191648

    Can you post the exact SQL query you are using (obfusticate table names if needed)?

    Confused by “select left after a substring” – I thought from original post you wanted left string up to first occurrence of ” ??? Mind you, second post contradicts that


    Dext
    Member
    #379115

    Sure so if I do a bog standard select

    [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=#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] [DB][/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][dbo][/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][Projects][/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]

    The results are [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Enquiries2014
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2014Test 1Folder 1
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2016Folder 2Folder 3Folder 4
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2017Folder 3Folder 4Folder 5
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2016Folder 35Folder 54Folder 55
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    So I modify that with a substring to remove the first 16 characters and starts on the 17th

    [CODE]
    [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=#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][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF]CHARINDEX[/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=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000]”[/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][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF]LEN[/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][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] [EDB][/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][dbo][/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][Projects][/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]

    Resulting in
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Test 1Folder 1
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 2Folder 3Folder 4
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 3Folder 4Folder 5
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 35Folder 54Folder 55
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    But what Im actually after is
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Test 1
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 2
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 3
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 35
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    Hope this clears up any confusion?

    Thanks[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]FROM[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px] [DB][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px].[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][dbo][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px].[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][Projects][/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]

    The results are [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Enquiries2014
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2014Test 1Folder 1
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2016Folder 2Folder 3Folder 4
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2017Folder 3Folder 4Folder 5
    [/TD]
    [/TR]
    [TR]
    [TD] Enquiries2016Folder 35Folder 54Folder 55
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    So I modify that with a substring to remove the first 16 characters and starts on the 17th

    [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=#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][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF]CHARINDEX[/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=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000][FONT=Consolas][SIZE=10px][COLOR=#FF0000]”[/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][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF][FONT=Consolas][SIZE=10px][COLOR=#FF00FF]LEN[/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][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] [EDB][/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][dbo][/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][Projects][/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]

    Resulting in
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Test 1Folder 1
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 2Folder 3Folder 4
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 3Folder 4Folder 5
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 35Folder 54Folder 55
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    But what Im actually after is
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Test 1
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 2
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 3
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 35
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    Hope this clears up any confusion?

    Thanks[CODE]
    [SIZE=10px][SIZE=10px][SIZE=10px]SELECT[/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][SIZE=10px]CHARINDEX[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px]([/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px][/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][SIZE=10px]LEN[/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][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] [EDB][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px].[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][dbo][/SIZE][/SIZE][SIZE=10px][SIZE=10px][SIZE=10px].[/SIZE][/SIZE][/SIZE][SIZE=10px][SIZE=10px][Projects][/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]

    Resulting in
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Test 1Folder 1
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 2Folder 3Folder 4
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 3Folder 4Folder 5
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 35Folder 54Folder 55
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    But what Im actually after is
    [TABLE=”border: 1, cellpadding: 1, width: 500″]
    [TR]
    [TD]
    [TABLE=”border: 0, cellpadding: 0, cellspacing: 0, width: 529″]
    [TR]
    [TD] Test 1
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 2
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 3
    [/TD]
    [/TR]
    [TR]
    [TD] Folder 35
    [/TD]
    [/TR]
    [/TABLE]

    [/TD]
    [/TR]
    [/TABLE]

    Hope this clears up any confusion?

    Thanks


    Ossian
    Moderator
    #191650

    Ah – my query (from your OP) was giving you the left part, so just the “enquiries 2013” etc.

    Let me look and think

    Is it always “Enquiresyyyy” ?


    Dext
    Member
    #379116
    Ossian;n510586 wrote:
    Ah – my query (from your OP) was giving you the left part, so just the “enquiries 2013” etc.

    Let me look and think

    Is it always “Enquiresyyyy” ?

    Cool thanks – and yes its always that format EnquiriesYYYYXXXXXXX…..


    Ossian
    Moderator
    #191651

    OK, on the assumption that your data always is in the form ‘Enquiriesyyyywanted dataunwanted data
    You will need to create a custom UDF

    CREATE FUNCTION dbo.GetTextBetweenSlashes (@text VARCHAR(100))

    RETURNS VARCHAR(100)

    AS

    BEGIN

    DECLARE @start INT

    DECLARE @end INT

    DECLARE @result VARCHAR(100)

    SET @result = ‘Not Found’

    SELECT @start = CHARINDEX(, @text,15) –based on assumption about data format

    SELECT @end = CHARINDEX (, @text, CHARINDEX(, @text,15)+1)

    IF @start 0 AND @end 0 — deals with inappropriate data

    BEGIN

    SELECT @result = SUBSTRING(@text, @start+1, @end @start1)

    END

    RETURN @result

    END

    You can use it in your Select statement:
    SELECT dbo.GetTextBetweenSlashes(Path)


    Dext
    Member
    #379117

    Thanks, Im not too ofay with SQL (not a newbie but not as experienced as yourself) is a function like a stored procedure or do you run it each time you query? I have read up on docs.micrsoft.com but the example and explanation is clear as mud and I haven’t got any headphones for a youtube audiovideo explanation :)

    Also given this DB is managed by a 3rd party application are there any issues creating a UDF? I know some support companies don’t like users messing with the DB (backend)


    Ossian
    Moderator
    #191652

    A function is like a stored proc except it cannot modify data – takes parameters, returns results etc.

    You will need permission to create the function in your database – IMHO give the support company the code and ask them to do it, alternatively create a new empty database and create the function there, then use the 3 part naming convention to access it (SELECT dbname.dbo.fnname(params)


    Dext
    Member
    #379118

    Thanks Ive asked if they have any issues with me adding additional functions will see what comes back. Ive never addedwritten functions before but I know from previous experience some companies get funny when you start delving into the back end (even though its just querying rather than modifyingwriting).

    Thanks again :)

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.