Post reply

How can i display the name of the folder from a field in a table.

  • December 28, 2010 at 12:44 pm

    #229895

    Right now i have a field like this, which is a combination of path and file name: /folder/folder1/folder2/12345-ABCD.txt

    I want to select LEVEL 3 section like this: /folder2/

    Thanks for any help this.

  • December 28, 2010 at 12:47 pm

    #1266893

    Use the CHARINDEX function

    eg from the BOL.

    DECLARE @document varchar(64)

    SELECT @document = 'Reflectors are vital safety' +

    ' components of your bicycle.'

    SELECT CHARINDEX('bicycle', @document)

    GO

  • December 28, 2010 at 12:55 pm

    #1266894

    Besides finding the Position of the folder. i would like to display the name of the folder from the LEVEL3

    the output should be: /folder2/

  • December 28, 2010 at 1:14 pm

    #1266905

    This works, but only if you don't get too deep with levels

    jim

    DECLARE @STR varchar(100)

    SET @STR = '/folder/folder1/folder2/12345-ABCD.txt'

    SELECT '/' + parsename(replace(replace(substring(@str,2,100),'.','|'),'/','.'),2) +'/'

  • December 28, 2010 at 1:24 pm

    #1266911

    you rock Jim.

    I just found other way.

    Select

    reverse(substring(reverse(PATH),charindex('/',reverse(PATH)), charindex('/',reverse(PATH),charindex('/',reverse(PATH))+1) - charindex('/',reverse(PATH))+1)) as foldername

    FROM table

  • December 28, 2010 at 1:30 pm

    #1266912

    You could also use the split function and get a list of all the folders:

    SELECT * FROM dbo.fn_Split('/folder/folder1/folder2/12345-ABCD.txt','/')

    WHERE IDX = 3

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • December 28, 2010 at 8:35 pm

    #1266995

    Sahasam (12/28/2010)

    you rock Jim.

    I just found other way.

    Select

    reverse(substring(reverse(PATH),charindex('/',reverse(PATH)), charindex('/',reverse(PATH),charindex('/',reverse(PATH))+1) - charindex('/',reverse(PATH))+1)) as foldername

    FROM table

    That's going to be a performance problem. REVERSE is one of the more "expensive" functions to use and you use it 6 times on each row. I'd chose one of the other methods.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 28, 2010 at 8:37 pm

    #1266996

    toddasd (12/28/2010)

    You could also use the split function and get a list of all the folders:

    SELECT * FROM dbo.fn_Split('/folder/folder1/folder2/12345-ABCD.txt','/')

    WHERE IDX = 3

    I notice you're using "fn_Split"... is that the splitter from MSDN that also has the WHILE loop in it?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 28, 2010 at 8:53 pm

    #1266999

    Actually no. I had to go back in my files and look, but it's the one that uses the tally table.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Viewing 9 posts - 1 through 8 (of 8 total)

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