April 23, 2012 at 11:36 am
How would you trim a string like below, and return only the second backslash and whatever follows?
'\Beverages\Soda Pop\Mountain Dew'
The value after the *first* backslash will not always be 'Beverages'. Sometimes it is 'Produce' ie it will vary in length.
I need to find functions that will discover the second backslash and return it plus whatever follows.
--Quote me
April 23, 2012 at 11:42 am
all ready been posted im looking for the thread now.
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 23, 2012 at 11:44 am
nope, it's different. That one wants the first segment of the path ie. Beverages. This one wants what comes after.
http://www.sqlservercentral.com/Forums/Topic1281987-392-1.aspx
--Quote me
April 23, 2012 at 11:46 am
polkadot (4/23/2012)
http://www.sqlservercentral.com/Forums/Topic1281987-392-1.aspx%5B/quote%5D
yep your right, off to modify that code real quick [facepalm]
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 23, 2012 at 11:53 am
WELL, I'm working on it myself, and certainly not expecting you to do it for me, but if you do you can be sure I'm still learning.
Thanks.
--Quote me
April 23, 2012 at 11:57 am
select substring('\Beverages\Soda Pop\Mountain Dew',patindex('%\%',substring('\Beverages\Soda Pop\Mountain Dew',patindex('%\%','\Beverages\Soda Pop\Mountain Dew')+1,100))+1,100)
April 23, 2012 at 12:00 pm
April 23, 2012 at 12:01 pm
Polka, try this:
declare @string varchar(200) = '\Beverages\Soda Pop\Mountain Dew'
select @string = STUFF( @string , 1 , CHARINDEX('\',@string,CHARINDEX('\',@string)+1),'')
April 23, 2012 at 12:03 pm
Thanks CC. My learning curve is steeper when I get help. It may look lazy but in fact I am not.
Thank you!!!!!
--Quote me
April 24, 2012 at 3:19 pm
Can you change the select statement so that if there is only one level in the path (ie. nothing after \Beverages), nothing is returned?
For example:
if instead of \Beverages\Soda Pop\Mountain Dew a row has only \Beverages, then nothing should be returned.
--Quote me
April 24, 2012 at 3:38 pm
How about these:
DECLARE @tststr VARCHAR(64);
SET @tststr = '\Beverages\Soda Pop\Mountain Dew';
SELECT SUBSTRING(RIGHT(@tststr,DATALENGTH(@tststr) - 1),1,PATINDEX('%\%',RIGHT(@tststr,DATALENGTH(@tststr) - 1)) - 1);
SELECT CASE WHEN PATINDEX('%_\%',@tststr) > 0 THEN SUBSTRING(@tststr,PATINDEX('%_\%',@tststr) + 1,DATALENGTH(@tststr) - PATINDEX('%_\%',@tststr) + 1) ELSE '' END;
SET @tststr = '\Beverages';
SELECT CASE WHEN PATINDEX('%_\%',@tststr) > 0 THEN SUBSTRING(@tststr,PATINDEX('%_\%',@tststr) + 1,DATALENGTH(@tststr) - PATINDEX('%_\%',@tststr) + 1) ELSE '' END;
April 24, 2012 at 3:50 pm
Try this 🙂
declare @string table ( string varchar(200) )
insert @string
select '\Beverages\Soda Pop\Mountain Dew'
union select '\Beverages'
union select '\\Food'
select s.string , crsapp.Idx
,CutStr =
Case when crsapp.Idx > 0 then STUFF(s.string,1,crsapp.Idx,'')
else ''
end
from @string s
cross apply( select CHARINDEX('\',s.string,CHARINDEX('\',s.string)+1)) crsapp (Idx)
April 24, 2012 at 4:48 pm
Thank you both.
trimming is a riddle, wrapped in a mystery, inside an enigma
--Quote me
April 24, 2012 at 6:35 pm
Polkadot - Hello again!
This is a minor adaptation of a solution I posted in another thread just a couple of days ago:
http://www.sqlservercentral.com/Forums/Topic1288432-392-1.aspx#bm1288618
Try this:
DECLARE @t TABLE (words VARCHAR(max))
INSERT INTO @t
SELECT '\Beverages\Soda Pop\Mountain Dew'
UNION ALL SELECT '\Beverages'
;WITH cte AS (
--SELECT SUBSTRING(words, 1, CHARINDEX(' ', words)) As word, words
SELECT '\' As word, words
FROM @t)
SELECT CASE CHARINDEX(word, SUBSTRING(words, 1+CHARINDEX(word, words, 1), LEN(words)), 1)
WHEN 0 THEN words
--ELSE SUBSTRING(words, 1, CHARINDEX(word, SUBSTRING(words, 1+CHARINDEX(word, words, 1), LEN(words)), 1)-1)
ELSE SUBSTRING(words, 1, CHARINDEX(word, SUBSTRING(words, 1+CHARINDEX(word, words, 1), LEN(words)), 1))
END As words
FROM cte
Should also solve the second question. Note that I left the original posting code in (besides setup data) but commented out.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 25, 2012 at 8:29 am
Hiya dwain.
Thanks for all the examples for my learning.
I have been studying the definition of each function and moving from the inner most nested one outward to understand the 'trim story'. In the meantime I was able to deliver on a mini report.
Million thanks.
--Quote me
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply