Post reply

find occurance and remove string

  • March 19, 2014 at 8:50 am

    #289605

    Hi,

    My sample data is as below.

    plan type A change from plan type B from Plan type C

    Insurance plan M changed from Insurance plan b From plan d from Plan N

    Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.

    plan type A change from plan type B

    Insurance plan M changed from Insurance plan b

    Please give an ideaa.

    Thanks

    Abhas.

  • March 19, 2014 at 9:00 am

    #1698407

    Would something like this help?

    WITH SampleData(String) AS(

    SELECT 'plan type A change from plan type B from Plan type C' UNION ALL

    SELECT 'Insurance plan M changed from Insurance plan b From plan d from Plan N'

    )

    SELECT LEFT(String, CHARINDEX( 'from', String, CHARINDEX( 'from', String) + 1) - 2)

    FROM SampleData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 19, 2014 at 9:04 am

    #1698410

    Or using the solution with OUTER APPLY:

    declare @table table (value varchar(500))

    insert into @table (value)

    values('plan type A change from plan type B from Plan type C')

    , ('Insurance plan M changed from Insurance plan b changing From plan d from Plan N')

    select value

    , first_from_pos

    , next_from_pos

    , left(value, next_from_pos-1) as final_result

    from @table

    outer apply (select charindex(' from ', value)) as search(first_from_pos)

    outer apply (select charindex(' from ', value, first_from_pos+1)) as search_from(next_from_pos)

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • March 19, 2014 at 11:39 pm

    #1698570

    Thanks Luis nad Hanshi. 🙂

    Its working fine for me. 🙂

    Thanks again

    Regards

    Abhas

Viewing 4 posts - 1 through 3 (of 3 total)

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