trimming a string

  • xenophilia (4/11/2012)


    when i run this:

    SELECT Distinct Outcome

    , TestNo

    , SUBSTRING(RIGHT('SuitePath',DATALENGTH('SuitePath') - 1),1,PATINDEX('%\%',RIGHT('SuitePath',DATALENGTH('SuitePath') - 1)) - 1)

    from testsuite

    i get this error

    Msg 536, Level 16, State 1, Line 1

    Invalid length parameter passed to the substring function.

    This usually indicates that you have some strings that don't match the pattern. In this case that means having fewer than 2 back slashes in the string. There are a couple of ways to get around this depending on the results you want in these cases. Try adding back slashes at the end to ensure that all strings have two.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (4/12/2012)


    xenophilia (4/11/2012)


    when i run this:

    SELECT Distinct Outcome

    , TestNo

    , SUBSTRING(RIGHT('SuitePath',DATALENGTH('SuitePath') - 1),1,PATINDEX('%\%',RIGHT('SuitePath',DATALENGTH('SuitePath') - 1)) - 1)

    from testsuite

    i get this error

    Msg 536, Level 16, State 1, Line 1

    Invalid length parameter passed to the substring function.

    This usually indicates that you have some strings that don't match the pattern. In this case that means having fewer than 2 back slashes in the string. There are a couple of ways to get around this depending on the results you want in these cases. Try adding back slashes at the end to ensure that all strings have two.

    Drew

    with out the code tags around its hard to see but he is running it on the literal string SuitePath there.

    SELECT Distinct Outcome

    , TestNo

    , SUBSTRING(RIGHT('SuitePath',DATALENGTH('SuitePath') - 1),1,PATINDEX('%\%',RIGHT('SuitePath',DATALENGTH('SuitePath') - 1)) - 1)

    from testsuite


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • i'm not a he and never have been "-)

    So, I am leaving this one alone. Thanks for assistance.

    -

Viewing 3 posts - 31 through 32 (of 32 total)

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