Post reply

return only end of column after certain character

  • September 17, 2008 at 10:18 am

    #198287

    Hi,

    I want to return only the data file name from the physical _name column in sys.master_files, omitting the directory structure;

    i.e from an entry such as

    F:\server\data\mssql\data\ProClarity_DashboardServer.mdf

    I would only want

    ProClarity_DashboardServer.mdf

    the length of filename, directory structure, or number of '\' characters is not consistent

    I have not been able to do this because I need to work backwards until I get to the first '\' character, can anyone help?

    ---------------------------------------------------------------------

  • September 17, 2008 at 10:29 am

    #873135

    the trick is to use the REVERSE function and charindex.

    I've broken it down step by step so you can see what's happening, along with a final long formula:

    [font="Courier New"]

    DECLARE @str VARCHAR(500)

    SET @str='F:\server\data\mssql\data\ProClarity_DashboardServer.mdf'

    PRINT REVERSE(@str) --returns  fdm.revreSdraobhsaD_ytiralCorP\atad\lqssm\atad\revres\:F

    PRINT CHARINDEX('\',REVERSE(@str)) --returns 31

    PRINT LEFT(REVERSE(@str),CHARINDEX('\',REVERSE(@str))) --returns fdm.revreSdraobhsaD_ytiralCorPPRINT REVERSE(LEFT(REVERSE(@str),CHARINDEX('\',REVERSE(@str)))) --\ProClarity_DashboardServer.mdf

    --final solution

    PRINT REVERSE(LEFT(REVERSE(@str),CHARINDEX('\',REVERSE(@str))-1))[/font]

    select physical_name,

    REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))-1))

    from sys.master_files

    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!

  • September 17, 2008 at 2:22 pm

    #873263

    lowell, you're a star. many thanks.

    george

    ---------------------------------------------------------------------

Viewing 3 posts - 1 through 2 (of 2 total)

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