How to delete...

  • Hello

    I have this value in a number of rows within a table, P406600; and i will like to delete the P character before the numerical value

    P406600 will become 406600

    How do i do that?

  • If all you want to do is remove the P character from the columns, then this will work

    UPDATE SomeTable

    SET SomeColumn = REPLACE(SomeColumn,'P','')

    WHERE SomeColumn LIKE 'P%'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/29/2008)


    If all you want to do is remove the P character from the columns, then this will work

    UPDATE SomeTable

    SET SomeColumn = REPLACE(SomeColumn,'P','')

    WHERE SomeColumn LIKE 'P%'

    Just in case the OP dosen't want to remove the P character from all rows and just wants to remove the P character from that explicit value:

    UPDATE SomeTable

    SET SomeColumn = REPLACE(SomeColumn,'P','')

    WHERE SomeColumn = 'P406600'

    I know I'm being pedantic but I'd hate to see the OP update any rows that begin with a P character but might yet be valid. 🙂

  • Can the letter be in any position of the string or will it always be the first? If it can be in any position you won't want to use REPLACE.

  • there are about 25,000 rows with the P character in front of the value, and what i want to do is to remove the P, in front of all values starting with it

    eg

    P1233 will become 1233

    P2589 will become 2589

    P7895 will become 7895

    P6548 will become 6548

    P9635 will become 9635

  • In that case Gail's suggestion will work for you.

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

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