Post reply

Spliting a string

  • July 3, 2008 at 4:14 am

    #93499

    Hi ,

    I have a string '135721|OL512K12M00003716|11C49EE2ED' , which is delimited by '|' . Now i want the last text i.e, 11C49EE2ED . But one more thing is that the string keeps changing , so we need to identify it through the delimiter only ( For e.g if the string is '1354|OL128K1M0001350|27effe89bd' , so the result should be '27effe89bd'

    Any help or suggestion is highly appreciated.

    Thanks in advance.:)

    Best Regards,
    TALIB ALI KHAN
    MCTS
    http://www.onlineasp.net

  • July 3, 2008 at 4:43 am

    #837705

    I'm sure someone will come up with a more elegant solution, but this should work (where @s-2 is the string you're looking to extract from)

    select reverse(substring(reverse(@s),0,charindex('|',reverse(@s))))

  • July 3, 2008 at 5:42 am

    #837739

    There is an excellent article on this site about splitting delimitted strings.

    http://www.sqlservercentral.com/articles/TSQL/62867/

    The article deals with csv but would be easy to change to '|'

  • July 4, 2008 at 12:23 am

    #838224

    The solution is here try this one.

    Declare @Delimiter CHAR(1),@InputString VARCHAR(100)

    SET @Delimiter = '|'

    SET @InputString = '135721|OL512K12M00003716|11C49EE2ED'

    SELECT REVERSE(SUBSTRING(REVERSE(@InputString),0,

    CHARINDEX(@Delimiter,REVERSE(@InputString),1)))

  • July 4, 2008 at 12:34 pm

    #838422

    Hi

    create function Split (

    @StringToSplit varchar(2048),

    @Separator varchar(128))

    returns table as return

    with indices as

    (

    select 0 S, 1 E

    union all

    select E, charindex(@Separator, @StringToSplit, E) + len(@Separator)

    from indices

    where E > S

    )

    select substring(@StringToSplit,S,

    case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String

    ,S StartIndex

    from indices where S >0

    Thanks -- Vj

    http://dotnetvj.blogspot.com

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • July 4, 2008 at 6:42 pm

    #838453

    Depending on the need, the following is about twice as fast... but it'll take a million rows to notice so let's just say it's very readable... 😀

    DECLARE @InputString VARCHAR(8000)

    SET @InputString = '135721|OL512K12M00003716|11C49EE2ED'

    SELECT RIGHT(@InputString,CHARINDEX('|',REVERSE(@InputString))-1)

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

  • July 5, 2008 at 2:20 am

    #838480

    Hey.. Thanks everyone for your valuable suggestions as i have already solved my problem. But anyways .. thanks again for showing concern.. 🙂

    Best Regards,
    TALIB ALI KHAN
    MCTS
    http://www.onlineasp.net

  • July 5, 2008 at 9:09 am

    #838505

    Talib Khan (7/5/2008)

    Hey.. Thanks everyone for your valuable suggestions as i have already solved my problem. But anyways .. thanks again for showing concern.. 🙂

    No problem.... two way street here, though... how did you solve your problem?

    --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 8 posts - 1 through 7 (of 7 total)

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