trimming a string

Question

trimming a string

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

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

drew.allen SSC Guru Points: 76945 More actions · Answer 1

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

CapnHector SSCoach Points: 16143 More actions · Answer 2

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]

xenophilia SSCommitted Points: 1891 More actions · Answer 3

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

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

-