Post reply

Select part of a text string...

  • October 26, 2015 at 11:28 am

    #399229

    I have a problem where I need to select all top level file paths from a string value in SQL

    So I have a column "Locations"

    Example Data:

    X:\folder\anotherfolder\

    X:\folder\yet another folder\

    X:\foldername\another folder\

    X:\foldername\yet another folder\

    I'd want to return only:

    X:\folder\

    X:\foldername\

    So I am guessing I need to somehow parse the sting and capture anything before the second '\'?

    Any help would be greatly appreciated!

    AMO AMAS AMATIT AGAIN

  • October 26, 2015 at 11:35 am

    #1835573

    here's one way to do it:

    since i want to find the second slash, i know i can use charindex to start at char 4 in your path.

    /*

    X:\folder\anotherfolder\X:\folderX:\folder\yet another folder\X:\folderX:\foldername\another folder\X:\foldernameX:\foldername\yet another folder\X:\foldername*/

    ;WITH MyCTE([Locations])

    AS

    (

    SELECT 'X:\folder\anotherfolder\' UNION ALL

    SELECT 'X:\folder\yet another folder\' UNION ALL

    SELECT 'X:\foldername\another folder\' UNION ALL

    SELECT 'X:\foldername\yet another folder\'

    )

    SELECT *,SUBSTRING([Locations],1,CHARINDEX('\', [Locations],4)) As TopFolder FROM MyCTE;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 26, 2015 at 11:44 am

    #1835575

    DECLARE @PATH TABLE (Locations VARCHAR(260));

    INSERT @PATH

    VALUES ('X:\folder\anotherfolder\'),

    ('X:\folder\yet another folder\'),

    ('X:\foldername\another folder\'),

    ('X:\foldername\yet another folder\');

    SELECT DISTINCT LEFT(Locations, CHARINDEX('\', Locations, 4)) AS 'Path'

    FROM @PATH;

    SpeedySQL

  • October 26, 2015 at 12:41 pm

    #1835586

    I tried this agains all 54,000+ rows and it returned correctly in seconds! Thanks so much!

    AMO AMAS AMATIT AGAIN

  • October 26, 2015 at 12:42 pm

    #1835587

    Thank-you for your time everyone!

    AMO AMAS AMATIT AGAIN

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply