extract part of string

  • if i have some values like

    aa ???

    dd

    jukin ???

    kdf ???

    dfe

    de

    d ???

    now i wish to extract values having ??? at the end but extract them without ???

    like i want the output be

    aa

    jukin

    ddf

    d

    Actually i wish to update the table values have ??? at the end wiht 'gon'

    so it should be like

    aa gon

    dd

    jukin gon

    kdg gon

    dfe

    de

    d gon

    can i use functions like substring() etc

  • shiwani2002sg (11/13/2008)


    if i have some values like

    aa ???

    dd

    jukin ???

    kdf ???

    dfe

    de

    d ???

    now i wish to extract values having ??? at the end but extract them without ???

    like i want the output be

    aa

    jukin

    ddf

    d

    Actually i wish to update the table values have ??? at the end wiht 'gon'

    so it should be like

    aa gon

    dd

    jukin gon

    kdg gon

    dfe

    de

    d gon

    can i use functions like substring() etc

    This should work:

    UPDATE YourTable

    SET yourColumn = REPLACE(yourColumn,'???','gon')

    WHERE LEFT(yourColumn,3) = '???'

    In fact you might not even need the WHERE clause, if you want to replace all occurrences of ??? even then it's not at the end of the string.

    [font="Verdana"]Markus Bohse[/font]

  • MarkusB (11/14/2008)


    shiwani2002sg (11/13/2008)


    if i have some values like

    aa ???

    dd

    jukin ???

    kdf ???

    dfe

    de

    d ???

    now i wish to extract values having ??? at the end but extract them without ???

    like i want the output be

    aa

    jukin

    ddf

    d

    Actually i wish to update the table values have ??? at the end wiht 'gon'

    so it should be like

    aa gon

    dd

    jukin gon

    kdg gon

    dfe

    de

    d gon

    can i use functions like substring() etc

    This should work:

    UPDATE YourTable

    SET yourColumn = REPLACE(yourColumn,'???','gon')

    WHERE LEFT(yourColumn,3) = '???'

    In fact you might not even need the WHERE clause, if you want to replace all occurrences of ??? even then it's not at the end of the string.

    WHERE LEFT(yourColumn,3) = '???'

    should be

    WHERE RIGHT(yourColumn,3) = '???'


    Madhivanan

    Failing to plan is Planning to fail

  • Oops, stupid mistake

    Madhivanan, thanks for correcting me.

    [font="Verdana"]Markus Bohse[/font]

  • thaks so much,, it worked

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

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