trimming a text string

  • 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

  • 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]

  • 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

  • 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]

  • 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

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

  • Polka, try this:

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

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

  • 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

  • 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

  • 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;

  • 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)

  • Thank you both.

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

    --Quote me

  • 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

  • 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