Post reply

how to print characters after third occurrance of a character

  • June 19, 2011 at 11:00 am

    #239448

    Hi all,

    I am trying to print characters after rd occurance of character 'a'.

    Suppose My column name is Emp_Name & one of the record like ababaccc.

    So i am trying to print ccc because it is after third occurance of char 'a'.

    How to achieve this one.

    Thanks in advance.

  • June 19, 2011 at 3:43 pm

    #1340909

    What is the real world problem you are trying to solve?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 19, 2011 at 8:14 pm

    #1340947

    roshan.zanwar (6/19/2011)

    Hi all,

    I am trying to print characters after rd occurance of character 'a'.

    Suppose My column name is Emp_Name & one of the record like ababaccc.

    So i am trying to print ccc because it is after third occurance of char 'a'.

    How to achieve this one.

    Thanks in advance.

    Will there always be exactly 3 occurances of "a"? If so, do a simple REVERSE, a CHARINDEX to find the "last a", and a RIGHT with the CHARINDEX# -1.

    I'd also be interested in what the real world application may be for this. Who knows? If you tell us, one of us might actually write the code for you to do this. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 20, 2011 at 6:05 am

    #1341105

    Hello,

    the CHARINDEX function will return you the first occurrence for the desired character, so you need invoke this functions three times in a row.

    WITH X AS (SELECT '-1-2-33' AS stream

    UNION SELECT '-1-2-33-45' AS stream

    UNION SELECT 'x-1-2' AS stream

    )

    SELECT CHARINDEX('-',stream, 1) AS FirstOccurrence

    , CHARINDEX('-',stream, CHARINDEX('-',stream, 1) + 1) AS SecondOccurrence

    , CHARINDEX('-',stream

    , CHARINDEX('-',stream, CHARINDEX('-',stream, 1) + 1) + 1) AS ThirdOccurrence

    , SUBSTRING(stream, NULLIF(CHARINDEX('-',stream

    , CHARINDEX('-',stream, CHARINDEX('-',stream, 1) + 1) + 1), 0) + 1

    , len(stream)) AS TextAfter

    FROM X

    This code (the SUBSTRING part of this code) will do the job for you. The NULLIF function will prevent any case when the desired character doesn't appears three times.

    Anyway I'm also curious about your real world use of this.

    Regards,

    Francesc

  • June 20, 2011 at 7:28 am

    #1341183

    That's why I wanted to know if there will only and always be just 3 occurances so we can use a singe CHARINDEX on the REVERSE image of the string to do this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 26, 2011 at 4:59 pm

    #1344265

    ... and, even after a week, I STILL want to know. Where the hell did the OP go? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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