trimming a text string

Question

trimming a text string

polkadot

SSChampion

Points: 13664
More actions
April 23, 2012 at 11:36 am

#256949

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

Viewing 15 posts - 1 through 15 (of 44 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

all ready been posted im looking for the thread now.

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]

polkadot SSChampion Points: 13664 More actions · Answer 2

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

CapnHector SSCoach Points: 16143 More actions · Answer 3

polkadot (4/23/2012)

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%5B/quote%5D

yep your right, off to modify that code real quick [facepalm]

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]

polkadot SSChampion Points: 13664 More actions · Answer 4

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

Geoff A SSChampion Points: 11511 More actions · Answer 5

select substring('\Beverages\Soda Pop\Mountain Dew',patindex('%\%',substring('\Beverages\Soda Pop\Mountain Dew',patindex('%\%','\Beverages\Soda Pop\Mountain Dew')+1,100))+1,100)

cbalasuriya SSC Journeyman Points: 86 More actions · Answer 6

Hi

try

http://www.sqlservercentral.com/Forums/Topic745511-8-2.aspx#bm747484

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Polka, try this:

declare @string varchar(200) = '\Beverages\Soda Pop\Mountain Dew'

select @string = STUFF( @string , 1 , CHARINDEX('\',@string,CHARINDEX('\',@string)+1),'')

polkadot SSChampion Points: 13664 More actions · Answer 8

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

polkadot SSChampion Points: 13664 More actions · Answer 9

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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;

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 11

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)

polkadot SSChampion Points: 13664 More actions · Answer 12

Thank you both.

trimming is a riddle, wrapped in a mystery, inside an enigma

--Quote me

Dwain Camps SSC Guru Points: 86938 More actions · Answer 13

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

polkadot SSChampion Points: 13664 More actions · Answer 14

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