Post reply

help with SQL syntax for SELECT stmt

  • August 22, 2016 at 8:57 am

    #312645

    --I need a SELECT statement that will output the part of a string between the first

    --and the very last "/"

    /HealthPlan/Health Services/ERVisits/ForActiveMembers

    Result: HealthPlan/Health Services/ERVisits/

    /HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services/ABC.rdl

    Result: HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services

    CREATE TABLE #t( path varchar(255) )

    INSERT INTO #t( path )

    Select '/HealthPlan/Health Services/ERVisits/ForActiveMembers'

    UNION

    Select '/HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services/ABC.rdl'

    So the output should look like

    HealthPlan/Health Services/ERVisits

    HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services

  • August 22, 2016 at 9:13 am

    #1896684

    PLEASE IGNORE THE ABOVE POST...

    I found the solution...( LISTED BELOW )

    Thanks for reading

    If object_id('tempdb..#t') IS NOT NULL DROP table #t;

    CREATE TABLE #t( path varchar(255) )

    INSERT INTO #t( path )

    Select '/HealthPlan/Health Services/ERVisits/ForActiveMembers'

    UNION

    Select '/HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services/ABC.rdl'

    --So the output should look like

    --HealthPlan/Health Services/ERVisits

    --HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services

    SELECT

    CASE WHEN CHARINDEX('/', REVERSE(path)) > 0 THEN

    LEFT( path, LEN(path ) - CHARINDEX('/', REVERSE(path)) )

    ELSE

    path

    END

    FROM

    #t;

  • August 22, 2016 at 10:09 am

    #1896699

    That won't return what you asked.

    To be sure about the correct formula:

    - Will the strings always start with a slash(/)?

    - What should be returned if there's only one?

    - What should be returned if there's none?

    In other words, what should be the output for this sample data?

    CREATE TABLE #t( path varchar(255) )

    INSERT INTO #t( path )

    Select '/HealthPlan/Health Services/ERVisits/ForActiveMembers'

    UNION ALL

    Select '/HealthPlan/Health Services/ERVisits/ForActiveMembers/PPD/Services/ABC.rdl'

    UNION ALL

    Select '/HealthPlan'

    UNION ALL

    Select 'ForActiveMembers'

    UNION ALL

    SELECT 'HealthPlan/Health Services';

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 22, 2016 at 11:03 am

    #1896712

    Sorry , my mistake.

    What I wanted was to get the path with out the file name .. So anything starting from char position 1 going all the way till we find the last "\" would be

    sufficient.

    So my code works... I tested.. All set !

  • August 22, 2016 at 11:17 am

    #1896716

    In that case, you don't need your CASE statement, the LEFT function alone will work fine. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 22, 2016 at 12:09 pm

    #1896727

    Agree

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

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