April 11, 2012 at 4:11 pm
Oh for the love of mankind it is still happening with the simple DDL I created. Apparently I'm not adopting Lynn's solution properly as it is happening here when I substitute column name.
SELECT Distinct Outcome
, TestNo
, SUBSTRING(RIGHT('SuitePath',DATALENGTH('SuitePath') - 1),1,PATINDEX('%\%',RIGHT('SuitePath',DATALENGTH('SuitePath') - 1)) - 1)
from testsuite
DDL
create table TestSuite
(
ID int
, TestNo varchar(10)
, SuitePath varchar (50)
, Outcome varchar (10)
)
insert into TestSuite
values
(1, 'test1', 'Beverages\Soda\Mountain Dew', 'Passed'),
(2, 'test2', 'Bulk\Grains\Oats', 'Failed'),
(3, 'test3', 'Dairy\Milk\Skim','NotExec'),
(4, 'test4', 'Bakery\Bread\Whole Wheat','Blocked'),
(5, 'test5', 'Produce\Fruits\Apples','Passed'),
(6, 'test6', 'Seasonal\Halloween\Candy','Passed')
;
-
April 11, 2012 at 4:23 pm
xenophilia (4/11/2012)
Oh for the love of mankind it is still happening with the simple DDL I created. Apparently I'm not adopting Lynn's solution properly as it is happening here when I substitute column name.SELECT Distinct Outcome
, TestNo
, SUBSTRING(RIGHT('SuitePath',DATALENGTH('SuitePath') - 1),1,PATINDEX('%\%',RIGHT('SuitePath',DATALENGTH('SuitePath') - 1)) - 1)
from testsuite
DDL
create table TestSuite(
ID int
, TestNo varchar(10)
, SuitePath varchar (50)
, Outcome varchar (10)
)
insert into TestSuite
values
(1, 'test1', 'Beverages\Soda\Mountain Dew', 'Passed'),
(2, 'test2', 'Bulk\Grains\Oats', 'Failed'),
(3, 'test3', 'Dairy\Milk\Skim','NotExec'),
(4, 'test4', 'Bakery\Bread\Whole Wheat','Blocked'),
(5, 'test5', 'Produce\Fruits\Apples','Passed'),
(6, 'test6', 'Seasonal\Halloween\Candy','Passed')
;
lynn's code RIGHT('SuitePath',DATALENGTH('SuitePath') - 1) creates a string that does not include the first character ("Beverages\Soda\Mountain Dew" becomes "everages\Soda\Mountain Dew") and it is like that because in your first post you said the pattern was like this "\Soda\Mountain Dew". so lynn got rid of the first \ then found the second position of the second \ and returned the substring. the code has to change now since you did not post sample data. however lynn's approach can be easily adapted so how about giving it a try and posting what you have.
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]
April 11, 2012 at 4:27 pm
No, for the original DDL that I posted, her trimming functions worked. They returned Beverages.
I still only need the string between the first set of backslashes
-
April 11, 2012 at 4:29 pm
DDL I meant to post
create table TestSuite
(
ID int
, TestNo varchar(10)
, SuitePath varchar (50)
, Outcome varchar (10)
)
insert into TestSuite
values
(1, 'test1', '\Beverages\Soda\Mountain Dew', 'Passed'),
(2, 'test2', '\Bulk\Grains\Oats', 'Failed'),
(3, 'test3', '\Dairy\Milk\Skim','NotExec'),
(4, 'test4', '\Bakery\Bread\Whole Wheat','Blocked'),
(5, 'test5', '\Produce\Fruits\Apples','Passed'),
(6, 'test6', '\Seasonal\Halloween\Candy','Passed')
;
-
April 11, 2012 at 4:30 pm
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.
-
April 11, 2012 at 4:39 pm
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.
you need to run this:
SELECT Distinct Outcome
, TestNo
, SUBSTRING(RIGHT(SuitePath,DATALENGTH(SuitePath) - 1),1,PATINDEX('%\%',RIGHT(SuitePath,DATALENGTH(SuitePath) - 1)) - 1)
from testsuite
the difference is changing 'SuitePath' to just SuitePath. with the single quotes, as gail posted earlier, you are running the substring function on the string SuitePath not the table column.
GilaMonster (4/11/2012)
Yes, but not in quotes.'DTS.SuitePath' means the literal string value DTS.SuitePath. If it's a column name, it must not be in quotes.
im guessing you cant follow simple instructions?
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]
April 11, 2012 at 4:52 pm
Yes, that's correct. I made that mistake. With the DDL I provided, it is working correctly. Ofcourse I should have run without quotes. My bad.
Anyway, without quotes, when I try to apply the same function to my column in real life it doesn't work...it returns empty cells.
The only difference between the DDL and real life is that the values I'm trying to trim can be 100 characters or more long. Any other ideas?
like this
\dfsadfdsfsadfdsafdsaf\popopsfdasfasdfds\erwrewaafadsfdsfsadfdsfsdfsadfdsafsdfsdfdsdfdfsadfdsafdsfwrerewrewrewrewrewre
-
April 11, 2012 at 4:56 pm
since it works on the data provided we can not go any further with out better data. please post some of the actual data you are running it on and we may be able to get it running correctly. the MindReader API is still vaporware at this stage.
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]
April 11, 2012 at 4:58 pm
xenophilia (4/11/2012)
like this\dfsadfdsfsadfdsafdsaf\popopsfdasfasdfds\erwrewaafadsfdsfsadfdsfsdfsadfdsafsdfsdfdsdfdfsadfdsafdsfwrerewrewrewrewrewre
once again that string works giving you dfsadfdsfsadfdsafdsaf when you run it through Lynn's parse
DECLARE @tststr VARCHAR(MAX);
SET @tststr = '\dfsadfdsfsadfdsafdsaf\popopsfdasfasdfds\erwrewaafadsfdsfsadfdsfsdfsadfdsafsdfsdfdsdfdfsadfdsafdsfwrerewrewrewrewrewre';
SELECT SUBSTRING(RIGHT(@tststr,DATALENGTH(@tststr) - 1),1,PATINDEX('%\%',RIGHT(@tststr,DATALENGTH(@tststr) - 1)) - 1);
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]
April 11, 2012 at 5:53 pm
xenophilia (4/11/2012)
No, for the original DDL that I posted, her trimming functions worked. They returned Beverages.I still only need the string between the first set of backslashes
I would like to get one thing straight, I am not her and never have been.
April 11, 2012 at 5:58 pm
Lynn Pettis (4/11/2012)
xenophilia (4/11/2012)
No, for the original DDL that I posted, her trimming functions worked. They returned Beverages.I still only need the string between the first set of backslashes
I would like to get one thing straight, I am not her and never have been.
the question is will you ever be?? :w00t::w00t::w00t:
to the actual thread. i thought you could use a break on answering the same question 6 different ways.
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]
April 11, 2012 at 6:05 pm
capn.hector (4/11/2012)
Lynn Pettis (4/11/2012)
xenophilia (4/11/2012)
No, for the original DDL that I posted, her trimming functions worked. They returned Beverages.I still only need the string between the first set of backslashes
I would like to get one thing straight, I am not her and never have been.
the question is will you ever be?? :w00t::w00t::w00t:
What???!!??!? And have my girls disown me? No way. :-P;-):w00t:;-):-P
April 11, 2012 at 6:15 pm
Lynn Pettis (4/11/2012)
capn.hector (4/11/2012)
Lynn Pettis (4/11/2012)
xenophilia (4/11/2012)
No, for the original DDL that I posted, her trimming functions worked. They returned Beverages.I still only need the string between the first set of backslashes
I would like to get one thing straight, I am not her and never have been.
the question is will you ever be?? :w00t::w00t::w00t:
What???!!??!? And have my girls disown me? No way. :-P;-):w00t:;-):-P
well when you did not rule it out it made me wonder a little :-):-D:-)
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]
April 11, 2012 at 7:13 pm
I am not catching the drift of that dialogue. Lynn is a man...I'm guessing. There was no offense meant. :sick:
Today was one of those days. Nothing worked and too many variables :-(. Sorry for the bad posts.
-
April 11, 2012 at 7:16 pm
xenophilia (4/11/2012)
I am not catching the drift of that dialogue. Lynn is a man...I'm guessing. There was no offense meant.Today was one of those days. Nothing worked and too many variables :-(. Sorry for the bad posts.
the banter between me an lynn was off topic but all your sample data works with lynn's posted code. so far i cant see what is causing it to fail when you run it on your system.
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]
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply